Row to Column Conversion Using case Statement

Database
Enthusiast

Row to Column Conversion Using case Statement

Hi Guys

 

Can some one help me to find the solution for this.

 

I developed query by using Union.

But I need query by using case statement.

 

This is sample data only. We have 20 above date columns and Billions of rows.

 

Thanks in Advance.

WhatsApp Image 2016-09-15 at 9.25.15 PM.jpeg


Accepted Solutions
Senior Supporter

Re: Row to Column Conversion Using case Statement

Select key,
max(case when dt = '2016-01-01' then t1 else null end) as t1,
max(case when dt = '2016-01-01' then dt else null end) as t1_d,
...
max(case when dt = '2016-01-30' then t30 else null end) as t30,
max(case when dt = '2016-01-30' then dt else null end) as t1_d,

from table
group by key
;

in case you have a changing date and want to get this a bit more dynamic...

Select key,
'2016-01-01' (date) as ref_date
max(case when dt = ref_date - 0 then t1 else null end) as t1,
max(case when dt = ref_date - 0 then dt else null end) as t1_d,
...
max(case when dt = ref_date - 29 then t30 else null end) as t30,
max(case when dt = ref_date - 29 then dt else null end) as t1_d,

from table
group by key
;

1 ACCEPTED SOLUTION
3 REPLIES
Senior Supporter

Re: Row to Column Conversion Using case Statement

Select key,
max(case when dt = '2016-01-01' then t1 else null end) as t1,
max(case when dt = '2016-01-01' then dt else null end) as t1_d,
...
max(case when dt = '2016-01-30' then t30 else null end) as t30,
max(case when dt = '2016-01-30' then dt else null end) as t1_d,

from table
group by key
;

in case you have a changing date and want to get this a bit more dynamic...

Select key,
'2016-01-01' (date) as ref_date
max(case when dt = ref_date - 0 then t1 else null end) as t1,
max(case when dt = ref_date - 0 then dt else null end) as t1_d,
...
max(case when dt = ref_date - 29 then t30 else null end) as t30,
max(case when dt = ref_date - 29 then dt else null end) as t1_d,

from table
group by key
;

Enthusiast

Re: Row to Column Conversion Using case Statement

Thank's For the Reply Ulrich..

Senior Supporter

Re: Row to Column Conversion Using case Statement

In case this solved  your question you might mark this as solved - will in a long term make this forum more usefull :-)