CODEWITHSUNDEEP
sqlsql-servertransformation
user12040497
user12040497

Reputation: 9

sql transformation - rows to column

i have been trying to solve this one image

my initial idea is like this

select name,
    CASE
    when count(name) = 1 then get first distinct value
    when count(name) = 2 then get first distinct value
    else get first distinct value
    END  as val1,
    CASE
    when count(name) = 1 then null
    when count(name) = 2 then get second distinct value
    else get second distinct value
    END as val2,
    CASE
    when count(name) = 1 then null
    when count(name) = 2 then null
    else get third distinct value
    END as val3
into desired_table
from source_table
group by name

is my attempt feasible? if so, how do i access the first, second and third distinct values?

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You can use conditional aggregation along with row_number():

select name,
       max(case when seqnum = 1 then value end) as value_1,
       max(case when seqnum = 2 then value end) as value_2,
       max(case when seqnum = 3 then value end) as value_3
into desired_table
from (select s.*,
             row_number() over (partition by name order by value) as seqnum
      from source_table s
     ) s
group by name;

Upvotes: 0

persian-theme
persian-theme

Reputation: 6638

use pivot . Your output table was incorrect. The correct form is available in db<>fiddle.

select name,x as value1,y as value2,z as value3
from
(
  select *
  from t1
) as SourceTable  
pivot
(
   max(value) for value in(x,y,z)
) as PivotTable

demo in db<>fiddle

Upvotes: 1

Related Questions