"Pivoting" a Table in SQL (i.e. Cross tabulation / crosstabulation)

Question

I'm working on trying to generate a report from a couple of database tables. The simplified version looks like this

---

Table = Campaign

CampaignID

Table = Source

Source_ID | Campaign_ID

Table = Content

Content_ID | Campaign_ID | Content_Row_ID | Content_Value

---

The report needs to read like this:

CampaignID - SourceID - ContentRowID(Value(A)) - ContentRowID(Value(B))

Where ContentRowID(Value(A)) means "Find a row the has a given CampaignID, and a ContentRowId of "A" and then get the ContentValue for that row"

Essentially, I have to "pivot" (I think that's the correct term) the rows into columns...

It's an Oracle 10g database...

Any suggestions?

Answer 1

This is my first stab at it. Refinement coming once I know more about the contents of the Content table.

First, you need a temporary table:

CREATE TABLE pivot (count integer);
INSERT INTO pivot VALUES (1);
INSERT INTO pivot VALUES (2);

Now we're ready to query.

SELECT campaignid, sourceid, a.contentvalue, b.contentvalue
FROM content a, content b, pivot, source
WHERE source.campaignid = content.campaignid
AND pivot = 1 AND a.contentrowid = 'A'
AND pivot = 2 AND b.contentrowid = 'B'

Answer 2

Example Content Table

     Content_ID   Campaign_ID   Content_Row_ID   Content_Value
        1             123          39100               A
        2             123          39200               B
        3             122          39100               C
        4             121          39100               D
        5             120          39300               E
        6             123          39300               F
        7             121          39300               G
        8             120          39200               H

which then I would like to translate to

     Campaign_Id    39100  39200   39300    
        120                  H        E
        121           D               G
        122           C           
        123           A      B        F

Answer 3

If you need a dynamic number of columns, I don't believe this can be done in standard SQL which, alas, exceeds my knowledge. But there are features of Oracle that can do it. I found some resources:

http://www.sqlsnippets.com/en/topic-12200.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063#41097616566309

Answer 4

If you have "Oracle, the Complete Reference" look for a section entitled, "Turning a Table on Its Side". This gives detailed examples and instructions for performing a pivot, although the edition I have doesn't call it a pivot.

Another term for "pivoting a table" is crosstabulation.

One of the easiest tools to use for performing crosstabulation is MS Access. If you have MS Access, and you can establish a table link from an Access database to your source table, you're already halfway there.

At that point, you can crank up the "Query Wizard", and ask it to build a crosstab query for you. It really is as easy as answering the questions the wizard asks you. The unfortunate side of this solution is that if look at the resulting query in SQL view, you'll see some SQL that's peculiar to the Access dialect of SQL, and cannot be used, in general, across other platforms.

You may also be able to download some simple analysis tools from the Oracle website, and use one of those tools to perform a crosstabulation for you.

Once again, if you really want to do it in SQL, "Oracle, the Complete Reference" should help you out.

Answer 5

If you don't have a dynamic number of columns and your dataset isn't too large you could do this...

SELECT CampaignID, SourceID, 
   (SELECT Content_Value FROM Content c 
      WHERE c.Campaign_ID=s.Campaign_ID 
      AND Content_Row_ID = 39100 
      AND rownum<=1) AS Value39100,
   (SELECT Content_Value FROM Content c 
      WHERE c.Campaign_ID=s.Campaign_ID 
      AND Content_Row_ID = 39200 
      AND rownum<=1) AS Value39200
FROM Source s;

Repeat the subquery for each additonal Content_Row_ID.

Answer 6

To do this in standard SQL, you do need to know all the distinct values of Content_Row_ID, and do a join per distinct value. Then you need a column per distinct value of Content_Row_ID.

SELECT CA.Campaign_ID, 
  C1.Content_Value AS "39100",
  C2.Content_Value AS "39200",
  C3.Content_Value AS "39300"
FROM Campaign CA
  LEFT OUTER JOIN Content C1 ON (CA.Campaign_ID = C1.Campaign_ID 
    AND C1.Content_Row_ID = 39100)
  LEFT OUTER JOIN Content C2 ON (CA.Campaign_ID = C2.Campaign_ID 
    AND C2.Content_Row_ID = 39200)
  LEFT OUTER JOIN Content C3 ON (CA.Campaign_ID = C3.Campaign_ID 
    AND C3.Content_Row_ID = 39300);

As the number of distinct values grows larger, this query becomes too expensive to run efficiently. It's probably easier to fetch the data more simply and reformat it in PL/SQL or in application code.

Answer 7

Bill Karwin mentions this, but I think this deserves to be pointed out very clearly:

SQL doesn't do what you're asking for, so any "solution" you get is going to be a kludge.

If you know, for sure, it's always going to run on an Oracle 10, then sure, Walter Mitty's crosstabulation might do it. The right way to do it is to work the easiest combination of sort order in the query and application code to lay it out right.

• It works on other database systems,
• it doesn't risk any other layers crapping out (I remember MySQL having a problem with >255 columns for instance. Are you sure you interface library copes as well as the db itself?)
• it's (usually) not that much harder.

If you need to, you can just ask for the Content_Row_IDs first, then ask for whatever rows you need, ordered by CampaignID, ContentRowID, which would give you each (populated) cell in left-to-right, line-by-line order.

---

Ps.

There are a bunch of stuff that modern man thinks SQL should have/do that just isn't there. This is one, generated ranges is another, recursive closure, parametric ORDER BY, standardised programming language... the list goes on. (though, admittedly, there's a trick for ORDER BY)

Answer 8

Bill Karwin and Anders Eurenius are correct that there is no solution that is straightforward, nor is there any solution at all when the number of resulting column values is not known in advance. Oracle 11g does simplify it somewhat with the PIVOT operator, but the columns still have to be known in advance and that doesn't meet the 10g criteria of your question.

Answer 9

If you don't know the number of columns up front just bring back a normal sql query and use server side code like I listed here: http://stackoverflow.com/questions/333181/a-question-about-datagrid-and-sql-query

Answer 10

Hi guys,

i have table like below ,

Product Name Price Date Apple 1.5 5/5/2009 Apple 3 5/6/2009 Apple 3.5 5/7/2009 Apple 2.5 5/8/2009 Apple 5.5 5/9/2009 Orange 10.5 5/5/2009 Orange 12.5 5/6/2009 Orange 7.5 5/7/2009 Orange 4.5 5/8/2009 Orange 5.5 5/9/2009

I need output like below

Product Name 5/5/2009 5/6/2009 5/7/2009 5/8/2009 5/9/2009

Apple 1.5 3 3.5 2.5 5.5 Orange 10.5 12.5 7.5 4.5 5.5

also date increases column also need to increase, Pls help me

Vickees

Answer 11

I Did a solution with this SQL. I Needed that the rows be the number of classes and the columns be the sumary of each classe by month, so, the first column is the sumary of row and each ohters columns are the sumary of each month, and the last row is the sumary of complete column month by month.

Good luck

Select DS.Cla, Sum(case when (Extract(year from DS.Data) =:intYear) then DS.PRE else 0 end) as ToTal, Sum(case when (Extract(month from DS.Data) =1) then DS.PRE else 0 end) as Jan, Sum(case when (Extract(month from DS.Data) =2) then DS.PRE else 0 end) as FEV, Sum(case when (Extract(month from DS.Data) =3) then DS.PRE else 0 end) as MAR, Sum(case when (Extract(month from DS.Data) =4) then DS.PRE else 0 end) as ABR, Sum(case when (Extract(month from DS.Data) =5) then DS.PRE else 0 end) as MAI, Sum(case when (Extract(month from DS.Data) =6) then DS.PRE else 0 end) as JUN, Sum(case when (Extract(month from DS.Data) =7) then DS.PRE else 0 end) as JUL, Sum(case when (Extract(month from DS.Data) =8) then DS.PRE else 0 end) as AGO, Sum(case when (Extract(month from DS.Data) =9) then DS.PRE else 0 end) as SETE, Sum(case when (Extract(month from DS.Data) =10) then DS.PRE else 0 end) as OUT, Sum(case when (Extract(month from DS.Data) =11) then DS.PRE else 0 end) as NOV, Sum(case when (Extract(month from DS.Data) =12) then DS.PRE else 0 end) as DEZ from Dados DS Where DS.Cla > 0 And Extract(Year from DS.Data) = :intYear group by DS.CLA

Union All

Select 0*count(DS.cla), 0*count(DS.cla), Sum(case when (Extract(month from DS.Data) =1) then DS.PRE else 0 end) as JAN, Sum(case when (Extract(month from DS.Data) =2) then DS.PRE else 0 end) as FEV, Sum(case when (Extract(month from DS.Data) =3) then DS.PRE else 0 end) as MAR, Sum(case when (Extract(month from DS.Data) =4) then DS.PRE else 0 end) as ABR, Sum(case when (Extract(month from DS.Data) =5) then DS.PRE else 0 end) as MAI, Sum(case when (Extract(month from DS.Data) =6) then DS.PRE else 0 end) as JUN, Sum(case when (Extract(month from DS.Data) =7) then DS.PRE else 0 end) as JUL, Sum(case when (Extract(month from DS.Data) =8) then DS.PRE else 0 end) as AGO, Sum(case when (Extract(month from DS.Data) =9) then DS.PRE else 0 end) as SETE, Sum(case when (Extract(month from DS.Data) =10) then DS.PRE else 0 end) as OUT, Sum(case when (Extract(month from DS.Data) =11) then DS.PRE else 0 end) as NOV, Sum(case when (Extract(month from DS.Data) =12) then DS.PRE else 0 end) as DEZ from Dados DS Where DS.Cla > 0 And Extract(Year from DS.Data) = :intYear