How to convert rows to columns using Cube, Pivot or Rollup SQL

Question

How to convert rows to columns using Cube, Pivot or Rollup SQL

I have a table with this information:

code    score    quality
123     2015      12
123     2016      16    
123     2017      14

I would like to show that

  code    2015    2016  2017
    123    12      16    14

Can you help me? Thanks to

+3

sql sql-server

Ric_hc May 12 '17 at 17:58

source to share

1 answer

GurV · Accepted Answer · 2017-05-12T18:01:31+0000

One way is to use conditional aggregation:

select code,
    max(case when score = 2015 then quality end) as [2015],
    max(case when score = 2016 then quality end) as [2016],
    max(case when score = 2017 then quality end) as [2017]
from your_table
group by code;

Demo

Or using PIVOT

:

select *
from your_table
pivot (
    max(quality) for score in ([2015],[2016],[2017])
) p;

How to convert rows to columns using Cube, Pivot or Rollup SQL

Demo

Demo

More articles: