CODEWITHSUNDEEP
sqloracle-databasepivot
daragh
daragh

Reputation: 537

How to pivot a SQL query on strings without dynamic query

I have a table like this:

ID         CODE
----------------------
1111       HE
1111       HO
1111       FR
2222       CH
2222       AA
2222       LM
2222       BB
3333       HO
3333       FR

and I want to return 1 row per id with 4 columns per row

I've read about dynamic SQL but have not used it before. As the number of columns is defined is a dynamic query required? Also will dynamic queries work within PL/SQL?

Expected:

ID         CODE_1     CODE_2    CODE_3    CODE_4
--------------------------------------------------
1111       HE         HO        FR        NULL
2222       CH         AA        LM        BB
3333       HO         FR        NULL      NULL

Upvotes: 1

Views: 47

Answers (1)

The Impaler
The Impaler

Reputation: 48865

If you want specifically 4 columns -- i.e. no dynamically growing or shrinking columns -- you can do:

with
n as (
  select
    id,
    code,
    row_number() over(partition by id order by code) as rn
  from t
)
select
  n0.id,
  n1.code as code_1,
  n2.code as code_2,
  n3.code as code_3,
  n4.code as code_4
from n n0
left join n n1 on n1.id = n0.id and n1.rn = 1
left join n n2 on n2.id = n0.id and n2.rn = 2
left join n n3 on n3.id = n0.id and n3.rn = 3
left join n n4 on n4.id = n0.id and n4.rn = 4
group by id

Upvotes: 1

Related Questions