SQL Server (2012): Pivot (or Group by?) on Multiple Columns

Question

I have the following table:

month   seating_id  dept_id
Jan     1           5
Jan     8           9
Jan     5           3
Jan     7           2
Jan     1           5
Feb     1           9
Feb     8           9
Feb     5           3
Feb     7           2
Feb     7           1

I want to count each type of seating_id and dept_id for each month, so the result would look something like this:

month   seating_id_1    seating_id_5    seating_id_7    seating_id_8    dept_id_1   dept_id_2   dept_id_3   dept_id_5   dept_id_9
Jan     2               1               1               1               0           1               1               2           1
Feb     0               1               2               1               1           1               1               0           2

I've experimented with unpivot/pivot and GROUP BY but haven't been able to achieve the desired results. Note, I would like to perform this as a SELECT statement, and not PROCEDURE call, if possible.

Let me know if you need any other info.

score 3 · Answer 1 · answered Dec 29 '17 at 18:07

In case you need to go Dynamic

Example

Declare @SQL varchar(max) = '
Select *
 From (
        Select month,B.*
         From  YourTable A
         Cross Apply (values (concat(''seating_id_'',seating_id),seating_id)
                            ,(concat(''dept_id_'',dept_id),dept_id)
                     ) b(item,value)
      ) A
 Pivot (count([Value]) For [Item] in (' + Stuff((Select Distinct concat(',[seating_id_',seating_id,']') from YourTable For XML Path('')),1,1,'')
                                       +','+
                                        Stuff((Select Distinct concat(',[dept_id_',dept_id,']') from YourTable For XML Path('')),1,1,'')
                                        + ') ) p'
Exec(@SQL);

Returns

The Generated SQL Looks like this

Select *
 From (
        Select month,B.*
         From  YourTable A
         Cross Apply (values (concat('seating_id_',seating_id),seating_id)
                            ,(concat('dept_id_',dept_id),dept_id)
                     ) b(item,value)
      ) A
 Pivot (count([Value]) For [Item] in ([seating_id_1],[seating_id_5],[seating_id_7],[seating_id_8],[dept_id_1],[dept_id_2],[dept_id_3],[dept_id_5],[dept_id_9]) ) p

score 2 · Answer 2 · answered Dec 29 '17 at 17:49

2

i have an answer you might not like but it does it:

select month
   , sum(case seating_id when 1 then 1 else 0 end) as seating_id_1
   , sum(case seating_id when 2 then 1 else 0 end) as seating_id_2
   ...
   , sum(case dept_id when 1 then 1 else 0 end) as dept_id_1
   , sum(case dept_id when 2 then 1 else 0 end) as dept_id_2
   ...
from YourTable
group by month

answered Dec 29 '17 at 17:49

KeithL

5,348
3
19
25

Definitely a good answer but it assumes I already know all IDs and no new ones are added. Is there a way to iterate over all available IDs for each column? The IDs come from separate tables (foreign key relationship) and can be referenced in the SELECT statement. – NickBraunagel Dec 29 '17 at 17:55
Yes. but it needs to be dynamic – KeithL Dec 29 '17 at 17:57

SQL Server (2012): Pivot (or Group by?) on Multiple Columns

2 Answers2