Help using Pivot

Andrew Skaggs 21 Reputation points
2021-11-15T21:21:39.67+00:00

If I have the following example table, how can I return just 2 rows with the coulmns being ID, then each persons name values columnized?

Create Table #yourtable
([ID] int, [NAME] varchar(5))
;

INSERT INTO #yourtable
([ID],[NAME])
VALUES
(1, 'John'),
(1, 'Kim'),
(1, 'Lisa'),
(2, 'Amy'),
(2, 'Josh'),
(2, 'Kim'),
(2, 'John')
;

I want the results to look like this, but also don't know how to title the column headers

ID
1 John Kim Lisa
2 Amy Josh Kim John

Can anyone help me solve this?

Thanks for your help

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 119.6K Reputation points
    2021-11-15T21:40:05.077+00:00

    If you do not need or dislike the dynamic queries, and the number of names is known and limited, then try this query:

    select *
    from 
    (
        select ID, [Name], C = concat('Name', row_number() over (partition by ID order by [Name]))
        from #yourtable
    ) t
    pivot
    (
        max(Name) for C in (Name1, Name2, Name3, Name4)
    ) p
    
    0 comments No comments

  2. Guoxiong 8,206 Reputation points
    2021-11-15T23:00:04.537+00:00

    You also can use STUFF function to get what you expected:

    SELECT  
        t.[ID],
        STUFF(
        ( 
            SELECT ' ' + [Name]
            FROM #yourtable
            WHERE  [ID] = t.[ID]
            FOR XML PATH(''), TYPE).value('.', 'varchar(1000)'),
            1,
            1,
            ''
        )  AS [Name] 
    FROM #yourtable AS t
    GROUP BY t.[ID];
    

  3. EchoLiu-MSFT 14,601 Reputation points
    2021-11-16T02:06:57.527+00:00

    Please also check:

    ;WITH cte  
    as(SELECT *,'Name'+CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS VARCHAR)rr   
      FROM #yourtable)  
      
    SELECT * FROM cte c  
    PIVOT (MAX(NAME) FOR rr IN ([Name1],[Name2],[Name3],[Name4])) p  
    

    Output:
    149603-image.png

    Regards,
    Echo


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.