Q&A for scholars and enthusiasts of literature
CREATE TABLE #yt ( [GDB_ARCHIVE_OID] int, [fireid] nvarchar(15), [subfireid] nvarchar(7), [firedate] datetime2(7), [GIS_area] numeric(38,8) ); INSERT INTO #yt ( [GDB_ARCHIVE_OID], [fireid], [subfireid], [firedate], [GIS_area] ) VALUES ('1130030', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.03300000'), ('1126764', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.03300000'), ('1126763', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.03300000'), ('1126762', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.03300000'), ('1126761', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.03300000'), ('1126760', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.04000000'), ('1126759', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.04000000'), ('1126758', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.04000000'), ('1126757', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.04000000'), ('1126756', 'DD/006/00', 'DD/006/', '2019-07-05 00:00:00.0000000', '0.04000000'), ('1127174', 'DE/036/00', 'DE/036/', '2019-07-04 00:00:00.0000000', '0.19400000'), ('1131728', 'DE/037/00', 'DE/037/', '2019-07-11 00:00:00.0000000', '0.54500000'), ('1125110', 'DH/016/00', 'DH/016/', '2019-07-04 00:00:00.0000000', '0.02800000'), ('1125109', 'DH/016/00', 'DH/016/', '2019-07-04 00:00:00.0000000', '0.02800000'), ('1125108', 'DH/016/00', 'DH/016/', '2019-07-04 00:00:00.0000000', '0.02400000'), ('1125107', 'DH/016/00', 'DH/016/', '2019-07-04 00:00:00.0000000', '0.02400000'), ('1125106', 'DH/016/01', 'DH/016/', '2019-07-04 00:00:00.0000000', '0.02200000'), ('1125105', 'DH/016/00', 'DH/016/', '2019-07-04 00:00:00.0000000', '0.02200000'), ('1125104', 'DH/016/00', 'DH/016/', '2019-07-04 00:00:00.0000000', '0.03200000'), ('1125103', 'DH/016/00', 'DH/016/', '2019-07-04 00:00:00.0000000', '0.03200000') select * from ( select [subfireid], [GDB_ARCHIVE_OID] from #yt ) src pivot ( max([GDB_ARCHIVE_OID]) for subfireid in ([DD/006/], [DE/036/], [DH/016/]) ) pvt; /* DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(subfireid) from #yt group by subfireid order by subfireid FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') print @cols; */