在SQL中“透视”一个表（即交叉制表/交叉制表）

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

我正在尝试从几个数据库表生成报告。简化版本看起来像这样

Campaign----------CampaignIDSource-----------------------Source_ID | Campaign_IDContent---------------------------------------------------------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"

其中ContentRowID(Value(A))表示“查找具有给定CampaignID的行,并且ContentRowId为”A“,然后获取该行的ContentValue”

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

基本上,我必须“旋转”(我认为这是正确的术语)行到列...

It's an Oracle 10g database...

这是一个Oracle 10g数据库......

Any suggestions?

9 个解决方案

#1

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

这是我第一次尝试。一旦我对Content表的内容有了更多的了解,我就会进行改进。

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.contentvalueFROM content a, content b, pivot, sourceWHERE source.campaignid = content.campaignidAND pivot = 1 AND a.contentrowid = 'A'AND pivot = 2 AND b.contentrowid = 'B'

#2

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

Bill Karwin提到了这一点,但我认为应该非常明确地指出:

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

SQL不能满足您的要求,因此您获得的任何“解决方案”都将成为一个障碍。

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.

如果你知道,它肯定会在Oracle 10上运行,那么肯定,Walter Mitty的交叉制表可能会这样做。正确的方法是在查询和应用程序代码中使用最简单的排序顺序组合来正确排列。

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?)

它不会冒任何其他层崩溃的风险(我记得MySQL有例如> 255列的问题。你确定你接口库应对以及数据库本身吗?)

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.

如果需要,您可以先询问Content_Row_ID,然后询问您需要的任何行,按CampaignID,ContentRowID排序,这将按从左到右的逐行顺序为您提供每个(已填充的)单元格。

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)

有一堆现代人认为SQL应该拥有/做的东西就是不存在。这是一个,生成的范围是另一个,递归闭包,参数ORDER BY,标准化编程语言......列表继续。 (不过,诚然,ORDER BY有一个技巧)

#3

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 Value39200FROM Source s;

Repeat the subquery for each additonal Content_Row_ID.

对每个additonal Content_Row_ID重复子查询。

#4

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.

要在标准SQL中执行此操作,您需要知道Content_Row_ID的所有不同值,并按每个不同的值执行连接。然后,每个不同的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.

随着不同值的数量变得越来越大,此查询变得过于昂贵而无法有效运行。更简单地获取数据并在PL / SQL或应用程序代码中重新格式化它可能更容易。

#5

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.

Bill Karwin和Anders Eurenius是正确的,没有解决方案是直截了当的,当预先不知道结果列值的数量时,也没有任何解决方案。 Oracle 11g确实使用PIVOT运算符进行了一些简化,但是这些列仍然必须提前知道,并且不符合您问题的10g标准。

#6

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:

如果你需要动态数量的列,我不相信这可以在标准SQL中完成,唉,超出我的知识。但是Oracle的功能可以做到这一点。我找到了一些资源:

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

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

#7

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.

如果您有“Oracle,完整参考”,请查找标题为“转动表格”的部分。这给出了执行数据透视的详细示例和说明,尽管我的版本并未将其称为支点。

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.

用于执行交叉制表的最简单工具之一是MS Access。如果您有MS Access,并且可以建立从Access数据库到源表的表链接,那么您已经在那里了一半。

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.

此时,您可以启动“查询向导”,并要求它为您构建交叉表查询。这真的就像回答向导问你的问题一样容易。这个解决方案的不幸之处在于,如果在SQL视图中查看结果查询,您将看到一些SQL的Access方言所特有的SQL,并且通常不能在其他平台上使用。

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.

您也可以从Oracle网站下载一些简单的分析工具,并使用其中一种工具为您执行交叉制表。

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

再一次,如果您真的想在SQL中执行此操作,“Oracle,完整参考”应该可以帮到您。

#8

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: Filling Datagrid And Sql Query

如果您不知道前面的列数只是带回正常的SQL查询并使用我在此处列出的服务器端代码:填充Datagrid和Sql查询

#9

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.

我用这个SQL做了一个解决方案。我需要行是类的数量,列是每个每个classe的sumary,所以,第一列是行的sumary,每个ohters列是每个月的sumary,最后一行是sumary完整列的逐月。

Good luck

Select DS.Cla,Sum(casewhen (Extract(year from DS.Data) =:intYear) then DS.PREelse 0end) as ToTal,Sum(casewhen (Extract(month from DS.Data) =1) then DS.PREelse 0end) as Jan,Sum(casewhen (Extract(month from DS.Data) =2) then DS.PREelse 0end) as FEV,Sum(casewhen (Extract(month from DS.Data) =3) then DS.PREelse 0end) as MAR,Sum(casewhen (Extract(month from DS.Data) =4) then DS.PREelse 0end) as ABR,Sum(casewhen (Extract(month from DS.Data) =5) then DS.PREelse 0end) as MAI,Sum(casewhen (Extract(month from DS.Data) =6) then DS.PREelse 0end) as JUN,Sum(casewhen (Extract(month from DS.Data) =7) then DS.PREelse 0end) as JUL,Sum(casewhen (Extract(month from DS.Data) =8) then DS.PREelse 0end) as AGO,Sum(casewhen (Extract(month from DS.Data) =9) then DS.PREelse 0end) as SETE,Sum(casewhen (Extract(month from DS.Data) =10) then DS.PREelse 0end) as OUT,Sum(casewhen (Extract(month from DS.Data) =11) then DS.PREelse 0end) as NOV,Sum(casewhen (Extract(month from DS.Data) =12) then DS.PREelse 0end) as DEZfrom Dados DSWhere DS.Cla > 0And Extract(Year from DS.Data) = :intYeargroup by DS.CLAUnion AllSelect 0*count(DS.cla),  0*count(DS.cla),Sum(casewhen (Extract(month from DS.Data) =1) then DS.PREelse 0end) as JAN,Sum(casewhen (Extract(month from DS.Data) =2) then DS.PREelse 0end) as FEV,Sum(casewhen (Extract(month from DS.Data) =3) then DS.PREelse 0end) as MAR,Sum(casewhen (Extract(month from DS.Data) =4) then DS.PREelse 0end) as ABR,Sum(casewhen (Extract(month from DS.Data) =5) then DS.PREelse 0end) as MAI,Sum(casewhen (Extract(month from DS.Data) =6) then DS.PREelse 0end) as JUN,Sum(casewhen (Extract(month from DS.Data) =7) then DS.PREelse 0end) as JUL,Sum(casewhen (Extract(month from DS.Data) =8) then DS.PREelse 0end) as AGO,Sum(casewhen (Extract(month from DS.Data) =9) then DS.PREelse 0end) as SETE,Sum(casewhen (Extract(month from DS.Data) =10) then DS.PREelse 0end) as OUT,Sum(casewhen (Extract(month from DS.Data) =11) then DS.PREelse 0end) as NOV,Sum(casewhen (Extract(month from DS.Data) =12) then DS.PREelse 0end) as DEZfrom Dados DSWhere DS.Cla > 0And Extract(Year from DS.Data) = :intYear

#1