SQL Server中一个特殊的交叉表查询

Question

SQL Server中一个特殊的交叉表查询

3

我读了一些关于如何将交叉表查询放入SQL Server的问题和答案。我尝试了这个方法，但是出现了错误。

简单的例子是这样的：我有一个M&M巧克力包装上的条形码，我想要一个SQL Server数据透视表，它可以将下面的信息转化为查询结果，行标题显示为条形码，列标题显示为不同的颜色。

我有一个查询/表格，其中包含以下内容：

| Package Barcode | Color | SomNmr
| 12345           | BLUE  | 3
| 12345           | RED   | 3
| 12345           | YELL  | 3
| 19999           | BLUE  | 24
| 19999           | BLUE  | 24
| 19999           | PINK  | 24
| 19999           | RED   | 24
| 19999           | RED   | 24

当我运行交叉表查询向导时，我选择以下选项： 你想将哪个字段值作为行标题？ 我选择包装条形码 你希望哪些字段值作为列标题？ 我选择颜色 你希望每个列和行的交叉点计算什么数字？ 我选择SomNmr和函数COUNT 交叉表正好提取了我需要查找的内容： picture of my crosstab

我查看了SQL代码并将其粘贴到SQL Server Management Studio中，但是出现了一些错误。

TRANSFORM Count(Table1.[SomNmr]) AS CountOfSomNmr
SELECT Table1.[Package Barcode], Count(Table1.[SomNmr]) AS [Total Of SomNmr]
FROM Table1
GROUP BY Table1.[Package Barcode]
PIVOT Table1.[Color];

我该如何在SQL Server上操作？我查阅了相关资料，发现可以使用case when语句，但是我似乎无法正确应用它来实现操作。非常感谢您的帮助。

如果有人想要详细了解我的情况，我已经制作了一段视频，我会找到方法将其发布在这里。

- vladyerus

研究SQL Server中的“PIVOT”。 - Kamil Gosciminski

谢谢Kamil！我一定会深入挖掘这个。已经看了不少YouTube视频了！ - vladyerus

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John Cappelletti · Accepted Answer

假设您需要动态的功能。

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Color) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
 Select [Package Barcode],[Total],' + @SQL + '
  From (
        Select [Package Barcode],B.[Color],B.[Cnt]
         From  YourTable A
         Cross Apply (
                        Select Color=A.Color,Cnt=1
                        Union All
                        Select Color=''Total'',Cnt=1
                     ) B
       ) A
 Pivot (Sum(Cnt) For [Color] in ([Total],' + @SQL + ') ) p'
Exec(@SQL);

返回

Package Barcode Total   BLUE    PINK    RED YELL
12345           3       1       NULL    1   1
19999           5       2       1       2   NULL

编辑 - 如果有帮助的话，生成的SQL看起来像这样

 Select [Package Barcode],[Total],[BLUE],[PINK],[RED],[YELL]
  From (
        Select [Package Barcode],B.[Color],B.[Cnt]
         From  YourTable A
         Cross Apply (
                        Select Color=A.Color,Cnt=1
                        Union All
                        Select Color='Total',Cnt=1
                     ) B
       ) A
 Pivot (Sum(Cnt) For [Color] in ([Total],[BLUE],[PINK],[RED],[YELL]) ) p

"EDIT 2" (第二次编辑)

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Color) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
 Select [Package Barcode],[Total],' + @SQL + '
  From (
        Select [Package Barcode],[Color],[Cnt]=case when Sum(Cnt)=0 then ''>>'' else cast(Sum(Cnt) as nvarchar(25)) end
         From  (
                Select [Package Barcode],[Color],[Cnt]=Sum(1) from YourTable Group By [Package Barcode],[Color]
                Union ALL
                Select Distinct [Package Barcode],C.[Color],[Cnt]=0
                 From  YourTable
                 Cross Join (Select Distinct Color From YourTable) C
                Union All
                Select [Package Barcode],[Color]=''Total'',[Cnt]=Sum(1) from YourTable Group By [Package Barcode]
               ) A
        Group By [Package Barcode],[Color]
       ) A
 Pivot (max(Cnt) For [Color] in ([Total],' + @SQL + ') ) p'
Exec(@SQL);

返回

Package Barcode Total   BLUE    PINK    RED YELL
12345           3       1       >>      1   1
19999           5       2       1       2   >>

遇到了显示不出 → 的问题，所以我放了一个 >> 作为占位符。

编辑3 - 条件聚合

Select [Package Barcode]
      ,[Total] = sum(1)
      ,[BLUE]  = sum(case when color='Blue' then 1 else 0 end)
      ,[Pink]  = sum(case when color='Pink' then 1 else 0 end)
      --.. Add more colors
 From YourTable 
 Group By [Package Barcode]