sql server 2008 pivot table

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。

感谢您的帮助。

谢谢，James

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

Answer 2

您的尝试过于复杂：）。此外，为列选择其他名称，而不是sum、group和number，因为这些名称不仅是sql语法关键字，还会使查询更难读取（例如sum（[sum]）、group-by-[group]）。

drop table #temp
GO
select 
    *
into #temp
from (
    select 'a' as [group],1 as [number],'-2503' as [sum],'WTH' as [source] union all
    select 'a',2,-180,'DET' union all
    select 'a',3,-156,'PLY' union all
    select 'a',4,-99,'DET' union all
    select 'a',5,-252,'DET' 
) x
GO   
select 
    [group], [number], 
    det, ply, wth
from #temp
pivot (
    sum([sum]) for [source] in (det,ply,wth)
) x