setequery

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

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/00', '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', 'Dc/016/', '2019-07-04 00:00:00.0000000', '0.03200000')
    
    DECLARE @cols AS NVARCHAR(MAX),
    @col2 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(subfireid) 
                    from #yt
                    group by subfireid
                    --order by subfireid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
            
select * from #yt

--set @col2 = concat('(', @cols, ')')
--print @cols
--print @col2
/*set @query = 'SELECT subfireid,[GDB_ARCHIVE_OID] from 
set @query = 'SELECT'+ @cols +', [GDB_ARCHIVE_OID] from 
             (
                select *
                from #yt
            ) x
            pivot 
            (
            max([GDB_ARCHIVE_OID])
            for subfireid in ([DD/006/], [DE/036/], [DE/037/], [DH/016/])
             ) p'
--([' + @col2 + ']) */
execute(@query);

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results