Search code examples
sqlreformat

SQL reformat row to column

Hi I have a table like:

c1 c2 c3 c4 c5
v1 xx xx a  1
v1 xx xx b  2
v2 xx xx a  3
v3 xx xx a  2
v3 xx xx b  1

I want to remove c4 and transfer c5 into 2 column based on c4 value:

c1 c2 c3 c5_a c5_b
v1 xx xx  1     2
v2 xx xx  3     0
v3 xx xx  2     1

How do I do this in SQL?

Solution

  • This is a slight tweak on vkp's answer, but it is a bit simpler:

    select c1, c2, c3,
       max(case when c4 = 'a' then c5 else 0 end) as c5_a,
       max(case when c4 = 'b' then c5 else 0 end) as c5_b
from t
group by c1, c2, c3;

    Also, it is unclear whether you want max() or sum().

    Note: This assumes that the xx values are the same in each row. Otherwise, you might need aggregation functions on those as well:

    select c1, max(c2) as c2, max(c3) as c3,
       max(case when c4 = 'a' then c5 else 0 end) as c5_a,
       max(case when c4 = 'b' then c5 else 0 end) as c5_b
from t
group by c1;