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

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

提问by Kivus

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

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

Campaign
----------
CampaignID

Source
-----------------------
Source_ID | Campaign_ID

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"

其中 ContentRowID(Value(A)) 表示“查找具有给定 CampaignID 和“A”的 ContentRowId 的行，然后获取该行的 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?

有什么建议？

采纳答案by Barry Brown

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.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'

回答by Anders Eurenius

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运行，那么肯定的是，沃尔特·米蒂的交叉表可能做到这一点。正确的做法是在查询和应用程序代码中使用最简单的排序顺序组合来正确布局。

• 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 librarycopes as well as the db itself?)
• it's (usually) not that much harder.

• 它适用于其他数据库系统，
• 它不会冒任何其他层崩溃的风险（例如，我记得 MySQL 有 >255 列的问题。你确定你的接口库和数据库本身一样处理吗？）
• 它（通常）没有那么难。

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_IDs，然后询问您需要的任何行，按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）

回答by Leigh Riffel

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 标准。

回答by Leigh Riffel

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.

对每个附加的 Content_Row_ID 重复子查询。

回答by Bill Karwin

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 或应用程序代码中重新格式化它可能更容易。

回答by Barry Brown

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://www.sqlsnippets.com/en/topic-12200.html

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

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

回答by Walter Mitty

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, the Complete Reference”，请查找标题为“Turning a Table on its Side”的部分。这提供了执行枢轴的详细示例和说明，尽管我的版本并未将其称为枢轴。

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 是 SQL 的 Access 方言所特有的，并且通常不能跨其他平台使用。

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, the Complete Reference”应该能帮到你。

回答by Walter Mitty

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 查询并使用我在此处列出的服务器端代码：Filling Datagrid And Sql Query

回答by Almeida

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 做了一个解决方案。我需要行是班级的数量，列是每个班级的按月的总结，所以，第一列是行的总结，每个 ohters 列是每个月的总结，最后一行是总结的完整列逐月。

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