Split rows with same ID into columns

Question

I have a table that looks like this:

ID    Name
1     John
1     Maria
1     Sam
2     Lisa
2     Martin

I would like to have an output like this:

ID    NAME1    NAME2    NAME3
1     John     Maria    Sam  
2     Lisa     Martin   NULL  

I don't want to use the pivot function because it won't give me this result (when tried this).

The ID will only generate 3 rows with the same id nr so there for only 3 name columns.

Finishing SP for this query:

INSERT INTO [Database].[dbo].[TEST]
(
       [ID],
       [Contact1],
       [Contact2],
       [Contact3]
)

;WITH CTE
AS 
(
SELECT 
    ID,
    NAMN,
    ROW_NUMBER() OVER(PARTITION BY P_ID ORDER BY NAMN) AS RowNum
FROM tabl1 
WHERE VISA_EJ = 0
)
SELECT 
    [ID],
    [1] AS [Contact1],
    [2] AS [Contact2],
    [3] AS [Contact3]
FROM CTE
PIVOT(MAX(NAMN) for RowNum in ([1],[2],[3])) AS [Contacts]

Still get an error with the semicolon.

Answer 1

You can use combination of window function and pivoting:

;with cte as(select id,
                    name, 
                    row_number() over(partition by id order by name) as rn
             from table_name)
 select id,
        [1] as name1,
        [2] as name2,
        [3] as name3
 from cte
 pivot(max(name) for rn in([1],[2],[3]))p

Alternatively you can do the same with subquery:

 select id,
        [1] as name1,
        [2] as name2,
        [3] as name3
 from (select id,
              name, 
              row_number() over(partition by id order by name) as rn
       from table_name) cte
 pivot(max(name) for rn in([1],[2],[3]))p

EDIT:

;WITH CTE
AS 
(
SELECT 
    ID,
    NAMN,
    ROW_NUMBER() OVER(PARTITION BY P_ID ORDER BY NAMN) AS RowNum
FROM tabl1 
WHERE VISA_EJ = 0
)

INSERT INTO [Database].[dbo].[TEST]
(
       [ID],
       [Contact1],
       [Contact2],
       [Contact3]
)

SELECT 
    [ID],
    [1] AS [Contact1],
    [2] AS [Contact2],
    [3] AS [Contact3]
FROM CTE
PIVOT(MAX(NAMN) for RowNum in ([1],[2],[3])) AS [Contacts]