How to turn bring columns in rows using sql query?

Question

I have the following result which i have got after using this query :

select SCHOOL_YEAR,COUNT(MUSIC) as ct,MUSIC from [table1]
group by SCHOOL_YEAR,MUSIC;

SCHOOL_YEAR   ct      MUSIC

2011          100     Piano
2011          50      Violin
2012          70      Piano
2012          150     Violin

I want it in the following format :

SCHOOL_YEAR   Piano     Violin

2011           100        50
2012           70         150

How can i achieve it ?

Please check the transpose in sql https://stackoverflow.com/questions/10699997/sql-server-transpose-rows-into-columns[enter link description here](https://stackoverflow.com/questions/10699997/sql-server-transpose-rows-into-columns) — zen, Jul 05 '17 at 06:21

score 0 · Accepted Answer · answered Jul 05 '17 at 06:23

0

Use a pivot query on the original table, but aggregate only on the school year:

SELECT
    SCHOOL_YEAR,
    SUM(CASE WHEN MUSIC = 'Piano'  THEN 1 ELSE 0 END) AS Piano,
    SUM(CASE WHEN MUSIC = 'Violin' THEN 1 ELSE 0 END) AS Violin
FROM table1
GROUP BY
    SCHOOL_YEAR

answered Jul 05 '17 at 06:23

Tim Biegeleisen

502,043
27
286
360

That would count the rows for each "music", instead of "THEN 1" it should be `music`. – Sloan Thrasher Jul 05 '17 at 06:31
@SloanThrasher The `MUSIC` column is text, not a number, read the question. The only issue here would be counting NULL values. My summation would ignore NULL, as would the `COUNT()` function. – Tim Biegeleisen Jul 05 '17 at 06:33
@TimBiegeleisen : I think instead of `1` and `0` should use `ct`. – Ullas Jul 05 '17 at 06:34
@TimBiegeleisen: You are right, my mistake. It should be ct and 0. – Sloan Thrasher Jul 05 '17 at 06:35
@Wanderer The original table has no such column called `ct`. _Read the question_. – Tim Biegeleisen Jul 05 '17 at 06:35

How to turn bring columns in rows using sql query?

1 Answers1