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

Question

当涉及到将交叉表查询放入 SQL Server 时，我已经阅读了其中一些问题和答案。我尝试这样做，但出现错误。

这个简单的例子是我在 m&m's 包装上有一个条形码我想要一个 SQL Server 数据透视表，如果你愿意的话，它将在下面获取这些信息并将其转换为一个查询，将行标题显示为条形码，并且列标题为不同的颜色。

我有一个 qry/table 有

| 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的函数

交叉表完全符合我的要求:

我查看了 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 上运行？我环顾四周并阅读了有关使用案例的信息，但我似乎无法正确应用它以使其正常工作。任何帮助表示赞赏。

我制作了一个视频，深入解释了我所拥有的内容，如果有人想看的话，我会找到如何将它发布到这里。

Answer 1

假设您需要 DYNAMIC

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

EDIT - If it helps, the SQL Generates looks like this

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

无法显示 →，所以我放了一个 >> 作为位置标记。

Edit 3 - Conditional Aggregation

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]