group by - SQL pivot rows to colums in SQL Server with grouping depending on a date

Question

As a novice to SQL I have been trying to write a query to organize the data correctly from a table in the format I can use.

The table looks like this:

opendate    servicename              servicequantity
-----------------------------------------------------
2021-01-08  Major Service                   15
2021-01-08  Minor Service                   19
2021-01-08  Tyre Service                    36
2021-01-08  Oil Change                      24
2021-01-09  Major Service                   15
2021-01-09  Minor Service                   19
2021-01-09  Tyre Service                    36
2021-01-09  Oil Change                      24
2021-01-11  Major Service                   15
2021-01-11  Minor Service                   19
2021-01-11  Tyre Service                    36
2021-01-11  Oil Change                      24

What I need to to have rows as a single date and the name and quantity next to it, as long as it is dynamic

ie 2021-01-11 Major Service 15 Minor Service 19 Tyre Service 36 Oil Change 24 -- and more depending if there are extra servicenames

Is this possible?

Thank you

question from:https://stackoverflow.com/questions/65598648/sql-pivot-rows-to-colums-in-sql-server-with-grouping-depending-on-a-date

Answer 1

I think you just want conditional aggregation:

select date,
       sum(case when servicename = 'Major Service' then servicequantity else 0 end) as major_service,
       sum(case when servicename = 'Minor Service' then servicequantity else 0 end) as minor_service,
       sum(case when servicename = 'Tyre Service' then servicequantity else 0 end) as tyre_service,
       sum(case when servicename = 'Oil Change' then servicequantity else 0 end) as oil_change
from t
group by date;