Creating a distinct list of names with dynamic columns

Question

I will try and keep this as concise and easy to understand as possible. I have a dataset which includes a large number of names, some are distinct, however some are not and all names have a corresponding reference number. Where the names are not distinct, I want to create a query that will display a distinct list all of names in that table, and have seperate columns that list listing the reference numbers of the names in the original dataset. Is this at all possible using SQL? I was thinking a PIVOT clause might be required, but not sure that would be appropriate

Like below;

Current Dataset

FullName	Reference
Joe Bloggs	T1234567
Joe Bloggs	T3456789
John Smith	T1234568
Sarah Edwards	T1234567
Karen Culford	T0999221
Sarah Edwards	T0239222
Joe Bloggs	T2045292

Desired Outcome

FullName	Reference1	Reference2	Reference3
Joe Bloggs	T1234567	T3456789	T2045292
John Smith	T1234568	NULL	NULL
Sarah Edwards	T1234567	T0239222	NULL
Karen Culford	T0999221	NULL	NULL

Which dbms are you using? (Some products have PIVOT, which can be useful here.) — jarlh, Feb 23 '22 at 16:33
Thanks, would you happen to have any suggestions on code for this? — pdwebb23, Feb 24 '22 at 10:34

SOS · Accepted Answer · 2022-02-28T14:10:26.363

If the number of pivot columns is unknown, you'd need dynamic sql (which has both pros and cons). Using this example as a base, first build a comma separated list of column names "Reference1,Reference2,....".

SQL Server 2017+

DECLARE @colList AS NVARCHAR(MAX)
   , @query  AS NVARCHAR(MAX);


; WITH colsByName AS (
   -- count how many columns per fullName
   SELECT FullName
        , Reference
        , ROW_NUMBER() over(PARTITION BY Fullname ORDER BY Reference) AS ColNum
   FROM   YourTable       
)
, uniqueColumns AS
(
    -- get unique column numbers
    SELECT DISTINCT ColNum
    FROM   colsByName
)
-- build comma separated list of names
SELECT @colList = STRING_AGG('Reference'+ CONVERT(VARCHAR, ColNum), ',')
FROM   uniqueColumns
;

Note, for SQL Server 2016 use STUFF instead of STRING_AGG

...
-- build comma separated list of names
SELECT @colList = STUFF((
            SELECT ',' + 'Reference'+ CONVERT(VARCHAR, ColNum)
            FROM  uniqueColumns
            ORDER BY ColNum
            FOR XML PATH('')
        )
        ,1,1,'') 
;

Then use it to build a dynamic PIVOT statement:

SET @query = 'SELECT FullName, ' + @colList + ' 
              FROM (
                     SELECT FullName
                             , Reference
                             , ''Reference''+ CONVERT(VARCHAR, ROW_NUMBER() over(PARTITION BY Fullname ORDER BY Reference)) AS ColNum
                     FROM   YourTable
              ) x
              PIVOT
              (
                  MAX(Reference)
                  FOR ColNum IN (' + @colList + ')
            ) p ';

EXECUTE(@query);

Creating a distinct list of names with dynamic columns

1 Answers1

Linked

FullName	Reference1	Reference2	Reference3
Joe Bloggs	3456789	T1234567	T2045292
John Hart
John Smith	T1234568
Karen Culford	T0999221
Sarah Edwards	T0239222	T1234567