A peculiar crosstab query in SQL Server

Question

I have read a few of these questions and answers when it comes to put a crosstab query into SQL Server. I tried doing this but I got errors.

The simple example is as is I have a barcode on a package of m&m's I want a SQL Server pivot table if you will that will take this information below and turn it into a query that shows the row headers as the barcode, and the column headers as the different colors.

I have a qry/table that has

| 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

When I run a crosstab query wizard, I run it with the following options:

Which Fields values do you want as row headings?

I select Package Barcode

Which Fields values do you want as column headings?

I choose Color

What number do you want calculated for each column and row intersection?

I choose SomNmr and the Function of COUNT

The crosstab pulls exactly what I was looking for:

I looked at the SQL code and paste it into SQL Server Management Studio but I get some errors.

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

How do I go about getting this to work on SQL Server? I was looking around and reading about using case when but I can't seem to apply that correctly to make it work. Any help appreciated.

I made a video in depth explaining what I have if anyone would like to see it I will find out how to post it on here.

Answer 1

Assuming you need 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);

Returns

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

Returns

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

Having trouble getting → to display, so I put a >> as a place marker.

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]