Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-15-2016
09:43 AM

09-15-2016
09:43 AM

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.

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-28-2016
12:00 PM

09-28-2016
12:00 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-28-2016
12:00 PM

09-28-2016
12:00 PM

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

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-28-2016
11:16 PM

09-28-2016
11:16 PM

Thank's For the Reply Ulrich..

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-29-2016
09:26 AM

09-29-2016
09:26 AM

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