CODEWITHSUNDEEP
sqlreformat
KarlTMuahaha T
KarlTMuahaha T

Reputation: 73

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?

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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;

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done with conditional aggregation, assuming the grouping columns are c1,c2,c3.

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

Upvotes: 1

Related Questions