-1

I have SQL code using many CTE statements to summerise data which eventually gets me to something like this

Bldgcode     Business        Seat_Count     Area  
A012345      Bus 1            1             1234  
A012345      Bus 2            3             3456  
B1234        Bus 1            4             6789  
B1234        Bus 3            6             4321

I want to transpose / pivot this to show a final result set that looks like this 

Bldgcode    Bus 1 Seat    Bus 1 Area    Bus 2 Seat    Bus 2 Area   Bus 3 Seat  Bus 3 Area  
A012345     1              1234           3             3456        0          0
B1234       4              6789           0              0          6          4321

I would like the Business columns to be dynamic as I have over 15 of them and don't want to list each individually, plus there may be a time when a new Business will appear in the data which I need another set of columns for.

I've looked at PIVOT but can't get it to work as my knowledge is limited, any help with coding would be greatly appreciated

Thanks

FredTheDog
  • 63
  • 1
  • 8
  • PIVOT operator is not dynamic - Look at https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – SQLRaptor Mar 29 '19 at 15:35

1 Answers1

2

Just use conditional aggregation:

select Bldgcode,
       max(case when Business = 'Bus 1' then Seat_Count end) as bus1_seats,
       max(case when Business = 'Bus 1' then area end) as bus1_area,
       max(case when Business = 'Bus 2' then Seat_Count end) as bus2_seats,
       max(case when Business = 'Bus 2' then area end) as bus2_area
from t
group by Bldgcode;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786