Click here to Skip to main content
14,298,693 members
Rate this:
Please Sign up or sign in to vote.
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:

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

Rate this:
Please Sign up or sign in to vote.

Solution 1

Try this:
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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100