SQL row values to columns

Question

I need help with something similar to PIVOT, but it isn't that easy.

I have table with this structure, and i need to get structure something like in the bottom of the picture:

PICTURE with exampe

Can anyone help me? Thank you guys!

i reckon just to do it manually.. like a case statement for each column — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Apr 19, 2018 at 20:24
What is the connection between CUSTOM_FIELD_ID and which CF column the value gets put in in the output? Also, is 2:value3 a typo? (Why 2 and not 3?) — user5683823
– user5683823, Commented Apr 19, 2018 at 20:26
Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Apr 19, 2018 at 20:26
@a_horse_with_no_name - before you can say that, you must understand the question. I don't. Do you? If you do, can you edit the post to clarify the question? If not, let's wait for clarification first. — user5683823
– user5683823, Commented Apr 19, 2018 at 20:28
@mathguy: it is a "standard" pivot question. The only "difference" is that the concatenated value of two columns is "pivoted" — user330315
– user330315, Commented Apr 19, 2018 at 20:32

Patrick H · Accepted Answer · 2018-04-19 20:52:56Z

Here is a solution using a pivot:

with testdata as
(select 'first' casekey, 1 custom_field_id, 'value1' custom_field_value from dual union all
 select 'first' casekey, 2 custom_field_id, 'value2' custom_field_value from dual union all
 select 'first' casekey, 3 custom_field_id, 'value3' custom_field_value from dual union all
 select 'second' casekey, 6 custom_field_id, 'value1' custom_field_value from dual union all
 select 'second' casekey, 9 custom_field_id, 'value1' custom_field_value from dual)

select *
from(
select casekey, custom_field_id, custom_field_value,
       rank() over(partition by casekey order by custom_field_id) pivotRank
from testdata)
pivot(
    max(custom_field_id || ':' || custom_field_value)
    for pivotRank in (1 as CF1, 2 as CF2, 3 as CF3, 4 as CF4, 5 as CF5)
)

First I use a windowing function to rank the custom_field_id column partitioned by casekey. Then all you have to do is take the max of the concatenated fields you wanted and pivot 1 through 5 on the pivotRank.

My output for the above query looks like this:

casekey     CF1         CF2         CF3         CF4         CF5
first       1:value1    2:value2    3:value3    (null)      (null)
second      6:value1    9:value1    (null)      (null)      (null)

Adam Silenko · Accepted Answer · 2018-04-19 20:48:02Z

0

try this:

with y as (
    select cas, cfi || ':' || cfv cf, ROW_NUMBER() over (partition by cas order by cfi) n
    from x)
  , z AS (
    select DISTINCT cas
    FROM x 
  )
select z.cas, cf1.cf, cf2.cf, cf3.cf, cf4.cf, cf5.cf
from z
left join y as cf1 on z.cas = cf1.cas and cf1.n = 1
left join y as cf2 on z.cas = cf2.cas and cf1.n = 2
left join y as cf3 on z.cas = cf3.cas and cf1.n = 3
left join y as cf4 on z.cas = cf4.cas and cf1.n = 4
left join y as cf5 on z.cas = cf5.cas and cf1.n = 5

answered Apr 19, 2018 at 20:48

Adam Silenko

3,1062 gold badges19 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL row values to columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related