Une requête de tableau croisé particulier dans SQL Server

J'ai lu quelques-unes de ces questions et réponses quand il s'agit de mettre une requête de tableau croisé dans SQL Server. J'ai essayé de le faire mais j'ai eu des erreurs.

L'exemple simple est comme je l'ai un code à barres sur un package de m & m je veux un tableau croisé dynamic de SQL Server si vous voulez prendre cette information ci-dessous et la transformer en une requête qui montre les en-têtes de ligne comme le code à barres, et les en-têtes de colonne comme les différentes colors.

J'ai un qry / table qui a

| 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

Lorsque j'exécute un assistant de requête de tableau croisé, je l'exécute avec les options suivantes:

Quelles valeurs de champs souhaitez-vous comme en-têtes de ligne?

Je choisis le package Barcode

Quelles valeurs de champs souhaitez-vous comme en-têtes de colonne?

Je choisis la couleur

Quel nombre voulez-vous calculé pour chaque intersection de colonnes et de rangées?

Je choisis SomNmr et la fonction de COUNT

Le tableau croisé tire exactement ce que je cherchais:

photo de mon tableau croisé

J'ai regardé le code SQL et le coller dans SQL Server Management Studio mais j'ai quelques erreurs.

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

Comment puis-je faire pour que cela fonctionne sur SQL Server? Je regardais autour de moi et je lisais à propos de l'utilisation de cas, mais je n'arrive pas à l'appliquer correctement pour le faire fonctionner. Toute aide appréciée.

J'ai fait une video en profondeur pour expliquer ce que j'ai si quelqu'un voulait le voir, je verrais comment l'afficher ici.

En supposant que vous avez besoin de 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);

Résultats

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

EDIT – Si cela aide, le SQL génère ressemble à ceci

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

Résultats

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

J'ai du mal à → afficher, donc j'ai mis un >> comme marqueur.

Edit 3 – Agrégation conditionnelle

 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]