sql server 2008数据透视表

Question

我有下表...

GROUP   Number  Sum     SOURCE
a           1   -2503   WTH
a           2   -180    DET
a           3   -156    PLY
a           4   -99     DET
a           5   -252    DET

我想介绍如下......

GROUP   Number  Sum      DET    PLY     WTH
a           1   -2503                   -2503
a           2   -180    -180        
a           3   -156           -156 
a           4   -99     -99     
a           5   -252    -252        

这是我使用 PIVOT 尝试过的(未成功)...

SELECT
    [GROUP]
    ,Number
    ,Opening_Val
    ,[Sum]
    ,DET
    ,PLY
    ,WTH
FROM
(SELECT 
     IA.GROUP_CD
    ,IA.Number
    ,IA.[sum]
    ,Src
FROM dbo.##Inter_App IA
GROUP BY IA.[GROUP]
    ,IA.Number
    ,IA.[sum]
    ,Src ) query
PIVOT 
(   Sum(IA.[Sum])
    For Src in (DET, PLY, WTH)
) pvt

理想情况下，我不希望将列限制为(DET、PLY、WTH)，因为可能有更多我不知道的 SOURCE。

感谢任何帮助。

谢谢，詹姆斯

Answer 1

您现有的查询有一些问题。

首先，我不确定为什么在您的子查询中对所有列使用 GROUP BY。这不是必需的，除非您知道在最终结果中有您不想要的重复项。

其次，您试图在最终选择列表中显示 [sum] 列，但您也在 PIVOT 中聚合此数据 -- Sum(IA.[Sum]) -- 除非您的子查询将此列列出两次，否则您无法聚合数据并显示此值。

第三，PIVOT 中使用的聚合引用了 IA 表别名 - 这个别名在子查询之外不可用，因此语法也不起作用。

我会更改您的查询以使用以下内容:

select [group], [number], [sum], DET, PLY, WTH
from
(
  select [group], [number], 
    [sum], 
    [sum] pivsum, SOURCE
  from dbo.Inter_App
) d
pivot
(
  sum(pivsum)
  for SOURCE in (DET, PLY, WTH)
) piv;

参见 SQL Fiddle with Demo .您会注意到子查询有两列带有 [sum] - 其中一列我给了一个别名 pivsum - 这将用于 PIVOT 中的聚合，另一列将用于最终选择列表。

最后，您声明您可能有未知数量的 Source 值，如果是这种情况，那么您将需要使用动态 SQL 来生成结果:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(SOURCE) 
                    from Inter_App
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [group], [number], 
                [sum], ' + @cols + ' 
            from 
            (
              select [group], [number], 
                [sum], 
                [sum] pivsum, SOURCE
              from dbo.Inter_App
            ) x
            pivot 
            (
                sum(pivsum)
                for SOURCE in (' + @cols + ')
            ) p '

execute sp_executesql @query;

参见 SQL Fiddle with Demo .两个版本都给出了结果:

| GROUP | NUMBER |   SUM |    DET |    PLY |    WTH |
|-------|--------|-------|--------|--------|--------|
|     a |      1 | -2503 | (null) | (null) |  -2503 |
|     a |      2 |  -180 |   -180 | (null) | (null) |
|     a |      3 |  -156 | (null) |   -156 | (null) |
|     a |      4 |   -99 |    -99 | (null) | (null) |
|     a |      5 |  -252 |   -252 | (null) | (null) |