Dynamically create columns in select SQL Oracle

Question

I want to show the investigator's name and title in one row.

How can I make the title column as title1, title2, title 3, or so on dynamically in SQL Oracle?

The number of titles can vary so if there is an investigator with 4 titles, then there would be 4 columns title1, title2, title3, title4.

This is my current query:

SELECT al.name, al.title_name FROM INVESTIGATOR a1

Answer 1

There is no max number for titles but by looking at the data I think I can set it to a fixed number

If you can pick a maximum number of titles then you don't need to do this dynamically.

If you apply a ranking to each title for each name, with something like:

select name,
  title_name,
  dense_rank() over (partition by name order by title_name) as rnk
from investigator

which puts them in alphabetic order, but you can choose a different order if you prefer; then you pivot the result of that query:

select *
from (
  select name,
    title_name,
    dense_rank() over (partition by name order by title_name) as rnk
  from investigator
)
pivot (
  max(title_name)
  for (rnk) in (
    1 as title1, 2 as title2, 3 as title3, 4 as title4
  )
)

I've gone with a maximum of four titles, but you can add as many as you think you might reasonably need.

how can I name my columns Title1, title2, title3...

I've done that using aliases in the pivot's in() clause.

With your example data that gives output:

NAME TITLE1 TITLE2 TITLE3 TITLE4
---- ------ ------ ------ ------
Abu  AP     AR     AS
Cu   TA
Gyu  AP

If you aren't on 11g or higher then you can do a manual pivot, which is essentiuall what Oracle is doing behind the scenes anyway:

select name,
  max(case when rnk = 1 then title_name end) as title1,
  max(case when rnk = 2 then title_name end) as title2,
  max(case when rnk = 3 then title_name end) as title3,
  max(case when rnk = 4 then title_name end) as title4
from (
  select name,
    title_name,
    dense_rank() over (partition by name order by title_name) as rnk
  from investigator
)
group by name

which gets the same result.

db<>fiddle