"Rotate" a table in SQL (i.e., Cross Tab / Cross Forward)

Question

I am working on creating a report from several database tables. A simplified version looks like this:

Campaign ---------- CampaignID Source ----------------------- Source_ID | Campaign_ID Content --------------------------------------------------------- Content_ID | Campaign_ID | Content_Row_ID | Content_Value 

The report should look like this:

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

Where ContentRowID (value (A)) means "Find a row that has a specific CampaignID and a ContentRowId from" A, then get the ContentValue for that row "

Essentially, I have to “rotate” (I think the right term) rows into columns ...

This is an Oracle 10g database ...

Any suggestions?

Answer 1

This is my first hit. Refinement occurs when 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 are ready to request.

 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

Bill Carwin mentions this, but I think it deserves a special mention:

SQL does not do what you ask for, so any "solution" you get will be kludge.

If you know for sure that it will always work on Oracle 10, then of course this can lead to Walter Mitty's cross-connections. The right way to do this is to use the easiest combination of sort order in the request code and application to position it correctly.

• It works with other database systems,
• it does not jeopardize any other layers (I remember that MySQL had a problem with s> 255 columns, for example. Are you sure that you are managing the library, as well as the db itself?)
• it is (usually) not much more complicated.

If you need, you can simply ask for Content_Row_ID first, and then ask about any rows you need, ordered by CampaignID , ContentRowID , which will give you each (populated) cell from left to right straight, in turn.

---

Ps.

There are a lot of things that modern people think that SQL should have / do something that simply does not exist. This is one, the generated ranges - this is another, recursive closure, parametric ORDER BY , standardized programming language ... the list goes on. (although, however, there is a trick for ORDER BY )

Answer 3

If you do not have a dynamic number of columns and your data set is not too large, you can 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 Content_Row_ID add-on.

Answer 4

To do this in standard SQL, you need to know all the different values ​​of Content_Row_ID and make the connection a separate value. Then you need a column for the individual Content_Row_ID value.

 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 different values ​​increases, this query becomes too costly to execute efficiently. It is probably easier to get the data easier and reformat them in PL / SQL or in the application code.

Answer 5

Bill Carvin and Anders Eurenius are true that there is no simple solution, and there is no solution at all when the number of column values ​​obtained is not known in advance. Oracle 11g simplifies it with the PIVOT operator , but the columns should still be known in advance and do not meet the 10g criteria of your question.

Answer 6

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

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

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

Answer 7

If you have “Oracle Full Link,” find the “Turn the table on your side” section. This gives detailed examples and instructions for executing a fulcrum, although the version I have does not call it a fulcrum.

Another term for "table rotation" is cross-forwarding.

One of the easiest tools to use cross-forwarding is MS Access. If you have MS Access, and you can set the link to the table from the Access database to the original table, you are halfway there.

At this point, you can run the Query Wizard and ask him to build a crosstab for you. It is as simple as answering the questions the wizard asks you. The unfortunate side of this solution is that if you look at the received query in the SQL view, you will see some SQL inherent to the SQL dialect, and cannot be used, in general, on other platforms.

You can also download some simple analysis tools from the Oracle website and use one of these tools to perform cross-forwarding for you.

Once again, if you really want to do this in SQL, "Oracle Full Link" will help you.

Answer 8

If you don’t know how many columns are ahead, just return a regular sql query and use the server side code, as I here: Populating Datagrid and Sql Query

Answer 9

I made a decision with this SQL. I need the rows to be the number of classes, and the columns to be total for each class unit by month, so the first column is the sum of the row, and each ocher column is the total of each month, and the last row is the total of the complete column 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