CODEWITHSUNDEEP
sqlsql-server
NickBraunagel
NickBraunagel

Reputation: 1599

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

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.

Upvotes: 0

Views: 55

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82000

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 enter image description here

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

Upvotes: 3

KeithL
KeithL

Reputation: 5594

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

Upvotes: 2

Related Questions