Transposing row data to columns

[adventurous1]

Hi all...

Hopefully somebody can help me with this question..

I am trying to write a "simple" report to track the number of visitors and dwell time (total and per page) by page navigation for a user visiting a website. The problem is is that I have row level data that I need to convert to columns. The data is in a similar format to this:

Visitor ID Page ID Page Name Dwell Time Begin Time
--- --- --- ------------ ----------
01 31 Home 18 12:00
01 32 Car 22 12:19
01 33 Truck 16 12:41
01 34 Bus 5 12:56
01 35 Review 35 01:01
01 36 Pay 15 01:36
01 37 Confirmation 23 01:51
02 31 Home 18 03:00
02 32 Car 63 03:18
02 35 Review 9 04:21
02 36 Pay 33 04:30

And needs to be converted (ideally) to this:

31 32 33 34 35 36 37 Count(Visitor ID) sum(DTime)
-- -- -- -- -- -- -- ----------------- ---------
x x x x x x x 1 124
x x x x 1 123


Can anyone help me with this? I think this could potentially be done via a PL/SQL procedure to transpose the rows but have no idea how to do it.

Any help anyone can offer is appreciated.

Thanks,

Adventurous1

 

[stefanhei]

Hi.
This can be done without PL/SQL. Assuming you have a given number of pages, I will post a query to count per visitor.

SELECT visitor_id,
  DECODE(SUM(DECODE(pgId,31,1,0)),0,'','X') p31,
  DECODE(SUM(DECODE(pgId,32,1,0)),0,'','X') p32,
  DECODE(SUM(DECODE(pgId,33,1,0)),0,'','X') p33,
  DECODE(SUM(DECODE(pgId,34,1,0)),0,'','X') p34,
  DECODE(SUM(DECODE(pgId,35,1,0)),0,'','X') p35,
  DECODE(SUM(DECODE(pgId,36,1,0)),0,'','X') p36,
  DECODE(SUM(DECODE(pgId,37,1,0)),0,'','X') p37,
  SUM(dtime) sdtime
  FROM your_table
 GROUP BY visitor_id

You may wish to add a WHERE-clause (by date). If you create a view using the above query you can count visitors like:

SELECT p31,p32,p33,p34,p35,p36,p37,
       COUNT(visitor_id),SUM(sdtime)
  FROM your_view
 GROUP BY p31,p32,p33,p34,p35,p36,p37

Stefan

 

[adventurous1]

Stefan,

Big thanks for the help....It gets me through my immediate danger.

The reason why I was looking for a PL/SQL function was that the number of values that this will be potentially used on is prohibitive to using a decode statement. Not sure if you can help further, but if so, it is appreciated.

Thanks,

Adventurous1

 

[SantaMufasa]

Adventurous,

When you say, "the number of values that this will be potentially used on is prohibitive...", are the number of values static or dynamic? Specifically, does the "universal set" of Page_IDs change from query to query or is the "univeral set" stable over a period of time?

If there is stability, why not create an Oracle VIEW that provides the display you seek? If you create a VIEW, no one should care how many DECODE statements are part of the VIEW definition.

When you say, "I think this could potentially be done via a PL/SQL procedure to transpose the rows..." you are right, but I anticipate that such a resolution would be no more efficient (probably even less efficient) than Stephan's excellent suggestion (implemented in the form of an Oracle VIEW.)

Let us know your reactions,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:03 (25May04) UTC (aka "GMT" and "Zulu"), 12:03 (25May04) Mountain Time)

 

[adventurous1]

Mufasa,

My talented friend...

I do like the idea of a view but unfortunately the nature of the data does not lend itself to such an apparent solution.

Unfortunately, the values will be dynamic based on the effects of processing prior to the load of this table.

Any thoughts are welcome.

Thanks,

Adventurous1

 

[SantaMufasa]

Adventurous,

Please tell me more so we can understand the "nature of the data (that) does not lend itself to (a VIEW-based) solution."

There are clues in your comments. When you say, "...the values will be dynamic based on the effects of processing prior to the load of this table," I infer that the table becomes populated as a result of a load process and is static until the next cycle that again populates the table. Is this a correct interpretation of your comment?

If this is the case, then I recommend a PL/SQL module subsequent to the table load that builds and executes a dynamic-SQL "CREATE OR REPLACE VIEW..." statement that displays the transposed output that applies to the current load cycle. So, the lifespan of the VIEW is between your table loads.

Does this tactic sound reasonable or am I missing something?

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:07 (26May04) UTC (aka "GMT" and "Zulu"), 18:07 (25May04) Mountain Time)

 

[adventurous1]

Mufasa,

That would be reasonable if I had the appropriate permissions to create such a view. Since I dont, I will need to execute this PL/SQL function as part of the processing I need to generate my report. I agree that it is not efficient but it is my only recourse to getting the job done.

 

[SantaMufasa]

Adventurous,

Under the constraints you must labor, then I recommend a "happy medium" of all the suggestions, above: Use either SQL or PL/SQL to generate yet another PL/SQL or SQL script (perhaps similar to the code in Stefanhei's post, above) to display the variable column information you need. And frankly, I don't believe you need to worry about your concern above, that your data is "prohibitive to using a decode statement".

Let us know what you chose for a solution,

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:49 (07Jun04) UTC (aka "GMT" and "Zulu"), 18:49 (06Jun04) Mountain Time)

 

[adventurous1]

The PL/SQL spawning another statement would work. I would have thought that this would be a fairly easy request, akin to a the creation of a simple pivot or crosstab in the Microsoft world. Too bad Oracle doesnt have a standard package to accomplish this.

Thanks,

Adventurous1

 

[SantaMufasa]

Adventurous,

Although people often refer to Oracle as the "Cadillac of Databases", it is not yet a Rolls-Royce. There are many features on a variety of even less-expensive "autos" that you and I know would improve the "Cadillac". I'm sure that Oracle will fill this (and other) gaps someday. But for now, you get to "roll your own" .

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:24 (08Jun04) UTC (aka "GMT" and "Zulu"), 18:24 (07Jun04) Mountain Time)

 

[adventurous1]

Which doesnt do PL/SQL novices much good...

 

[SantaMufasa]

Adventerous,

Au contraire, mon ami. This represents a great opportunity for PL/SQL novices to become PL/SQL pros. If you have an unavoidable business need for pivot/crosstab, then you sit down and program a solution. Some of my greatest PL/SQL programming insights have come from lack of "built-ins" and my needing to program the solution.

Just think, if you are the one that programs good pivot/crosstab solution, then you are the one that earns the bragging rights and also becomes the PL/SQL guru!

As my late dad used to say, "Look at it as an opportunity, m'boy!"

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:06 (09Jun04) UTC (aka "GMT" and "Zulu"), 19:06 (08Jun04) Mountain Time)

 

[adventurous1]

I was looking for somebody who had this developed already, as my request can hardly be unique. Why reinvent the wheel?