Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
796 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...