Click here to Skip to main content
15,945,876 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
ID            Name
1             aaa
1             bbb
1             ccc
2             xxx
2             yyy
2             zzz


i want to have something like this below
ID            Name          Name2          Name3
1             aaa             bbb               ccc
2             xxx             yyy               zzz


What I have tried:

SQL
SELECT id, [1] as name1, [2] as name2, [3] as name3
FROM
(
SELECT id, [name], rank() over (partition by id order by [name]) AS RANK
FROM temp
) pvt
PIVOT
(
MAX([NAME])
FOR RANK IN ([1],[2],[3])
) A
Posted
Updated 10-Sep-19 2:21am
v2
Comments
Maciej Los 10-Sep-19 7:52am    
Java?!? This is SQL!
Please, improve your question and type correct tag.

1 solution

Try this:
SQL
SELECT [1] AS Name1, [2] AS Mame2, [3] AS Name3
FROM (
    SELECT [name], RANK() OVER (PARTITION BY id ORDER BY [name]) AS RowNo
    FROM temp
    ) DT
PIVOT(MAX([name]) FOR RowNo IN ([1],[2],[3])) PVT
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900