Move name in one column to multiple colums in SQL/Oracle

Question

I have two records shown below:

Id     User_name   User_id
DT122  Doe, John   123
DT122  Yum, Mi     124

How do I write in either Oracle query that can display the result as below

Id     UserName1    Username2
DT122  Doe, John    Yum, Mi

Any feedback, ideas?

This isn't a group_concat issue it just looks that way because of the format of names. It is just a cross tab or pivot. — Sean Lange, Nov 12 '15 at 15:43
@SeanLange Pivot probably, question it is has to be dynamic or not — Lukasz Szozda, Nov 12 '15 at 15:46
To clarifiy the pivot quesitons: how many ID's are the same each time? Just 2? if so a simple join would work. If variable, then you would need to write a Dynamic Pivot statement like [This](http://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) or [This](http://stackoverflow.com/questions/16978047/dynamic-oracle-pivot-in-clause) — xQbert, Nov 12 '15 at 15:49

score 0 · Answer 1 · answered Nov 12 '15 at 15:50

Here is how you could do this in sql server. This is known as a crosstab, some people call this a conditional aggregate. Another option would be to use a PIVOT. I find the syntax for a crosstab less obtuse than pivot and it generally has a very slight advantage for performance.

You can read more about this technique here. http://www.sqlservercentral.com/articles/T-SQL/63681/. Or if you don't know how many columns you will need to do a dynamic version of this. You can read about that here. http://www.sqlservercentral.com/articles/Crosstab/65048/

create table #Something
(
    Id char(5)
    , User_name varchar(20)
    , User_id int
)

insert #Something
select 'DT122', 'Doe, John', 123 union all
select 'DT122', 'Yum, Mi', 124

select Id
    , MAX(case when RowNum = 1 then User_Name end) as UserName1
    , MAX(case when RowNum = 2 then User_Name end) as UserName2
from
(
    select ID
        , USER_NAME
        , USER_ID
        , ROW_NUMBER() over (PARTITION BY Id order by user_name) as RowNum
    from #Something
) x
group by x.Id

drop table #Something

Move name in one column to multiple colums in SQL/Oracle

1 Answers1