SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE YourTable
(
   FullName varchar(40)
   , Reference varchar(8)
);

INSERT INTO YourTable (FullName, Reference)
VALUES 
('Joe Bloggs','T1234567')
, ('Joe Bloggs','3456789')
, ('John Smith','T1234568')
, ('Sarah Edwards','T1234567')
, ('Karen Culford','T0999221')
, ('Sarah Edwards','T0239222')
, ('Joe Bloggs','T2045292')
, ('John Hart',NULL)
;
8 rows affected

SELECT FullName
        , Reference
        , ROW_NUMBER() over(PARTITION BY Fullname ORDER BY Reference) AS RowNum
FROM   YourTable       


FullName
Reference
RowNum


Joe Bloggs
3456789
1

Joe Bloggs
T1234567
2

Joe Bloggs
T2045292
3

John Hart
null
1

John Smith
T1234568
1

Karen Culford
T0999221
1

Sarah Edwards
T0239222
1

Sarah Edwards
T1234567
2


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 = STUFF((
			SELECT ',' + 'Reference'+ CONVERT(VARCHAR, ColNum)
			FROM  uniqueColumns
			ORDER BY ColNum
			FOR XML PATH('')
		)
		,1,1,'') 
;

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);



FullName
Reference1
Reference2
Reference3


Joe Bloggs
3456789
T1234567
T2045292

John Hart
null
null
null

John Smith
T1234568
null
null

Karen Culford
T0999221
null
null

Sarah Edwards
T0239222
T1234567
null

FullName	Reference	RowNum
Joe Bloggs	3456789	1
Joe Bloggs	T1234567	2
Joe Bloggs	T2045292	3
John Hart	null	1
John Smith	T1234568	1
Karen Culford	T0999221	1
Sarah Edwards	T0239222	1
Sarah Edwards	T1234567	2