代码之家 › 专栏 › 技术社区 › Peter M

如何使用SQL数据透视?

pivot tsql sql-server-2005 sql-server

1

Peter M · 技术社区 · 17 年前

我有一个按以下方式组织的数据集:

Timestamp|A0001|A0002|A0003|A0004|B0001|B0002|B0003|B0004 ...
---------+-----+-----+-----+-----+-----+-----+-----+-----
2008-1-1 |  1  |  2  | 10  |   6 |  20 |  35 | 300 |  8
2008-1-2 |  5  |  2  |  9  |   3 |  50 |  38 | 290 |  2    
2008-1-4 |  7  |  7  | 11  |   0 |  30 |  87 | 350 |  0
2008-1-5 |  1  |  9  |  1  |   0 |  25 | 100 |  10 |  0
...

其中,a001是项目1的值a,b001是项目1的值b。一个表中可以有60多个不同的项目,每个项目都有一个值列和一个B值列,这意味着表中总共有120多个列。

我想得到的是一个3列结果(项目索引、A值、B值),它对每个项目的A值和B值求和:

Index | A Value | B Value
------+---------+--------
 0001 |   14    |   125
 0002 |   20    |   260
 0003 |   31    |   950
 0004 |    9    |    10
 ....

当我从一列转到另一行时,我希望在解决方案中有一个支点,但我不确定如何充实它。部分问题是如何去掉a和b来形成索引列的值。另一部分是,我以前从来没有使用过透视图,所以我也对基本语法感到困惑。

我认为,最终我需要一个多步骤的解决方案,首先将总结构建为:

ColName | Value
--------+------
A0001   |  14
A0002   |  20
A0003   |  31
A0004   |   9
B0001   | 125
B0002   | 260
B0003   | 950
B0004   |  10

然后修改colname数据以去掉索引:

ColName | Value | Index | Aspect
--------+-------+-------+-------
A0001   |  14   | 0001  |  A
A0002   |  20   | 0002  |  A
A0003   |  31   | 0003  |  A
A0004   |   9   | 0004  |  A
B0001   | 125   | 0001  |  B
B0002   | 260   | 0002  |  B
B0003   | 950   | 0003  |  B
B0004   |  10   | 0004  |  B

最后自我连接,将b值向上移动到a值旁边。

为了得到我想要的东西,这似乎是一个冗长的过程。所以我在寻求建议,无论我是朝着正确的方向前进,还是有另一种方法让我的生活变得如此容易,我已经仔细研究过了。

注1)解决方案必须在MSSQL2005的T-SQL中。

注2)表格的格式不能更改。

编辑我考虑过的另一种方法是在每一列上使用union和individual sum()s:

SELECT '0001' as Index, SUM(A0001) as A, SUM(B0001) as B FROM TABLE
UNION
SELECT '0002' as Index, SUM(A0002) as A, SUM(B0002) as B FROM TABLE
UNION
SELECT '0003' as Index, SUM(A0003) as A, SUM(B0003) as B FROM TABLE
UNION
SELECT '0004' as Index, SUM(A0004) as A, SUM(B0004) as B FROM TABLE
UNION
...

但这种方法也不太好看

编辑到目前为止,有两种很好的反应。但我想在查询中再添加两个条件:-)

1)我需要根据时间戳的范围(minv<timestamp<maxv)选择行。

2)我还需要在处理时间戳的UDF上有条件地选择行。

使用Brettski的表名,上面的内容会转换为:

...
(SELECT A0001, A0002, A0003, B0001, B0002, B0003 
 FROM ptest 
 WHERE timestamp>minv AND timestamp<maxv AND fn(timestamp)=fnv) p
unpivot
(val for item in (A0001, A0002, A0003, B0001, B0002, B0003)) as unpvt
...

考虑到我已经有条件地添加了fn()需求,我认为我还需要按照jonathon的建议执行动态SQL路径。尤其是当我必须为12个不同的表构建相同的查询时——都是相同的样式。

2 回复 | 直到 14 年前

1

5

Jonathan DeMarks 17 年前

同样的答案,很有趣:

-- Get column names from system table
DECLARE @phCols NVARCHAR(2000)
SELECT @phCols = COALESCE(@phCols + ',[' + name + ']', '[' + name + ']') 
    FROM syscolumns WHERE id = (select id from sysobjects where name = 'Test' and type='U')

-- Get rid of the column we don't want
SELECT @phCols = REPLACE(@phCols, '[Timestamp],', '')

-- Query & sum using the dynamic column names
DECLARE @exec nvarchar(2000)
SELECT @exec =
'
    select
        SUBSTRING([Value], 2, LEN([Value]) - 1) as [Index],
        SUM(CASE WHEN (LEFT([Value], 1) = ''A'') THEN Cols ELSE 0 END) as AValue, 
        SUM(CASE WHEN (LEFT([Value], 1) = ''B'') THEN Cols ELSE 0 END) as BValue
    FROM
    (
        select *
        from (select ' + @phCols + ' from Test) as t
        unpivot (Cols FOR [Value] in (' + @phCols + ')) as p
    ) _temp
    GROUP BY SUBSTRING([Value], 2, LEN([Value]) - 1)
'
EXECUTE(@exec)

您不需要在这一个中硬编码列名。

2

1

Brettski 17 年前

好吧,我想出了一个解决方案,可以让你开始。这可能需要一些时间来整理,但会表现良好。如果我们不必按名称列出所有列,那就太好了。

基本上,这是使用UNPIVOT并将该产品放入临时表中,然后将其查询到最终数据集中。当我把这个放在一起时,我把我的表命名为ptest,这是一个包含所有a001等列的表。

-- Create the temp table
CREATE TABLE #s (item nvarchar(10), val int)

-- Insert UNPIVOT product into the temp table
INSERT INTO  #s (item, val)
SELECT item, val
FROM
(SELECT A0001, A0002, A0003, B0001, B0002, B0003
FROM ptest) p
unpivot
(val for item in (A0001, A0002, A0003, B0001, B0002, B0003)) as unpvt

-- Query the temp table to get final data set
SELECT RIGHT(item, 4) as item1,
Sum(CASE WHEN LEFT(item, 1) = 'A' THEN val ELSE 0 END) as A,
Sum(CASE WHEN LEFT(item, 1) = 'B' THEN val ELSE 0 END) as B
from #s
GROUP BY RIGHT(item, 4)

-- Delete temp table 
drop table #s

顺便说一句,谢谢你的提问,这是我第一次使用非Pivot。一直想要,只是从来没有需要。