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 stackoverflow.com/questions/10699997/…enter link description here — zen
– zen, Commented Jul 5, 2017 at 6:21

Tim Biegeleisen · Accepted Answer · 2017-07-05 06:23:44Z

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 5, 2017 at 6:23

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sloan Thrasher Over a year ago

That would count the rows for each "music", instead of "THEN 1" it should be music.

Tim Biegeleisen Over a year ago

@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.

Ullas Over a year ago

@TimBiegeleisen : I think instead of 1 and 0 should use ct.

Sloan Thrasher Over a year ago

@TimBiegeleisen: You are right, my mistake. It should be ct and 0.

Tim Biegeleisen Over a year ago

@Wanderer The original table has no such column called ct. Read the question.

Collectives™ on Stack Overflow

How to turn bring columns in rows using sql query?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related