“枢转” SQL中的表（即交叉表/交叉表） [英] "Pivoting" a Table in SQL (i.e. Cross tabulation / crosstabulation)

问题描述

我正在努力从几个数据库表生成报表。简化版本如下所示

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

报表需要这样读取：

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

其中ContentRowID（Value（A））表示查找具有给定CampaignID和ContentRowId为A的行，然后获取该行的ContentValue

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

Oracle 10g数据库...

It's an Oracle 10g database...

任何建议？

推荐答案

是我的第一刺。细化一旦我知道更多关于内容表的内容。

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

现在我们可以查询了。

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'

这篇关于“枢转” SQL中的表（即交叉表/交叉表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！