How to pivot a SQL query on strings without dynamic query

Question

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

The Impaler · Accepted Answer · 2019-05-02 12:01:57Z

1

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

answered May 2, 2019 at 12:01

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pivot a SQL query on strings without dynamic query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related