Need Help with Query

General

Need Help with Query

Hi

Can we merge/ pivot 2 columns like the below :

This how the data is:

ID     ID-2    KEY-1     KEY-2       SUBKEY-1      SUBKEY-2

ABC   123     ?             ?              567                768

ABC   123    200         214              ?                   ?

Here is what i'm looking for :

ID ID-2 KEY-1 KEY-2 SUBKEY-1 SUBKEY-2

ABC 123 200 214 567  768

I was able to achieve this

SEL

ID,

ID-2,

MAX(CASE WHEN CONDITION THEN KEY-1 ELSE NULL END) AS KEY-1,

MAX(CASE WHEN CONDITION THEN KEY-2 ELSE NULL END) AS KEY-2,

MAX(CASE WHEN CONDITION THEN SUBKEY-1 ELSE NULL END) AS SUBKEY-1,

MAX(CASE WHEN CONDITION THEN SUBKEY-2 ELSE NULL END) AS SUBKEY-2

from

     TEST_TABLE

GROUP BY 1,2

My Question is, in this case I only get 2 rows. SO I was able to achieve it.

How can I handle this scenario when there are multiple columns involved and how should I try to merge them?

Please help.

1 REPLY
Enthusiast

Re: Need Help with Query

Need to be careful with data type:

select id,id1,

max(coalesce(key1,0))over(partition by id,id1 order by id,id1 rows between unbounded preceding and unbounded following) k1,max(coalesce(key2,0))over(partition by id,id1 order by id,id1 rows between unbounded preceding and unbounded following) k2,

max(coalesce(subkey1,0))over(partition by id,id1 order by id,id1 rows between unbounded preceding and unbounded following) sk1,

max(coalesce(subkey2,0))over(partition by id,id1 order by id,id1 rows between unbounded preceding and unbounded following) sk2

from yor_table qualify row_number() over(partition by id,id1 order by id,id1)=1