“转动"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...

有什么建议吗?

推荐答案

这是我第一次尝试.一旦我对 Content 表的内容有了更多了解，就会进行细化.

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屋！