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

You can pivot, as you seem to know from the tags; but Is there a maximum number of title values per name? If there is then it doesn't need to be dynamic. And does it matter how they are ordered? It's unclear if they are in alphabetic order, random (and happens to be alphabetic in the example), or there is another column that determines their priority. — Alex Poole, Jun 09 '21 at 13:49
Thanks Alex.. but how can I name my columns Title1, title2, title3...? Pivot would name the columns as AP, AR, AS, right? The order of titles doesn't matter. There is no max number for titles but by looking at the data I think I can set it to a fixed number...If there aren't any titles it would just show blank. — may, Jun 09 '21 at 14:32

score 1 · Accepted Answer · answered Jun 09 '21 at 14:46

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

Dynamically create columns in select SQL Oracle

1 Answers1