SQL Pivot Issue, Need dynamic column names

Question

Alright. So we needed to get output as down with the code below. I got most of the code from: Convert Rows to columns using 'Pivot' in SQL Server. This gives me the dynamic column names for the years and then counts the events for each year. I need (or at least would like) for the column names to formatted as FY 2014 Total etc. instead of just 2014. So created another list of column names to be used as the output names. I figured that when doing the pivot you would be giving the custom names for the new columns. I'm not sure what I would need to do.

DECLARE @colValues as NVARCHAR(MAX),
        @colNames as NVARCHAR(MAX),
        @query as NVARCHAR(MAX)

--Creates the column names dynamically from the FY results
select @colValues = STUFF((
                                         SELECT ',' + QUOTENAME(FY)
                                         From [dbo].[Table]
                                         Group By FY
                                         Order By FY desc
                                  for xml path(''), TYPE
                                  ).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @colNames = STUFF((
                                         SELECT ',' + '[FY ' + Cast(FY as NVARCHAR(4)) + ' Total]'
                                         From [dbo].[Table]
                                         Group By FY
                                         Order By FY desc
                                  for xml path(''), TYPE
                                  ).value('.', 'NVARCHAR(MAX)'),1,1,'')
--print(@cols)

--Creates the SQL Query using pivot with the dynamic columns
set @query = '
                       with temp1 as
                       (
                             Select a.EventType,
                                         a.Days_Since_Last_Event,
                                         row_number() over (partition by a.EventType order by a.Days_Since_Last_Event) as ranking
                             From 
                             (
                                    SELECT EventType
                                           ,DATEDIFF(day,EventDate,GETDATE()) AS Days_Since_Last_Event
                                    FROM [dbo].[Table]
                              ) a join 
                             (
                                    SELECT EventType
                                           ,DATEDIFF(day,EventDate,GETDATE()) AS Days_Since_Last_Event
                                    FROM [dbo].[Table]
                              ) b on a.EventType = b.EventType and a.Days_Since_Last_Event = b.Days_Since_Last_Event
                       ), temp3 as
                       (             
                           select EventType,
                                     Days_Since_Last_Event
                           from temp1
                           where ranking = 1
                       ), temp2 as
                       (
                             Select EventType
                                         ,'+ @colNames + '
                             From 
                             (
                                  Select EventType, FY, 1 as thing
                                  From [dbo].[Table]
                             ) a
                             Pivot
                             (
                                  Count(thing)
                                  for FY in (' + @colValues + ')
                             ) b
                       )

                           Select t3.*, '+ @colNames +'
                           From temp3 t3 
                                  join temp2 t2 on t3.EventType = t2.EventType'


execute(@query);

JamieD77 · Answer 1 · 2015-11-06T21:33:11.390

use @colValues in your temp2 cte in both places.. then use this in your final select.

select @colNames = STUFF((
                                 SELECT ',' + QUOTENAME(FY) + ' AS [FY ' + Cast(FY as NVARCHAR(4)) + ' Total]'  
                                 From [dbo].[Table]
                                 Group By FY
                                 Order By FY desc
                           for xml path(''), TYPE
                          ).value('.', 'NVARCHAR(MAX)'),1,1,'')

should give you something like this..

DECLARE @colValues as NVARCHAR(MAX),
        @colNames as NVARCHAR(MAX),
        @query as NVARCHAR(MAX)

--Creates the column names dynamically from the FY results
select @colValues = STUFF((
                            SELECT ',' + QUOTENAME(FY)
                            From [dbo].[Table]
                            Group By FY
                            Order By FY desc
                    for xml path(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @colNames = STUFF((
                           SELECT ',' + QUOTENAME(FY) + ' AS [FY ' + Cast(FY as NVARCHAR(4)) + ' Total]'
                           From [dbo].[Table]
                           Group By FY
                           Order By FY desc
                   for xml path(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)'),1,1,'')
--Creates the SQL Query using pivot with the dynamic columns
set @query = '
with temp1 as
(
        Select a.EventType,
                    a.Days_Since_Last_Event,
                    row_number() over (partition by a.EventType order by a.Days_Since_Last_Event) as ranking
        From 
        (
            SELECT EventType
                    ,DATEDIFF(day,EventDate,GETDATE()) AS Days_Since_Last_Event
            FROM [dbo].[Table]
        ) a join 
        (
            SELECT EventType
                    ,DATEDIFF(day,EventDate,GETDATE()) AS Days_Since_Last_Event
            FROM [dbo].[Table]
        ) b on a.EventType = b.EventType and a.Days_Since_Last_Event = b.Days_Since_Last_Event
), temp3 as
(             
    select EventType,
                Days_Since_Last_Event
    from temp1
    where ranking = 1
), temp2 as
(
        Select EventType
                    ,'+ @colValues + '
        From 
        (
            Select EventType, FY, 1 as thing
            From [dbo].[Table]
        ) a
        Pivot
        (
            Count(thing)
            for FY in (' + @colValues + ')
        ) b
)

    Select t3.*, '+ @colNames +'
    From temp3 t3 
            join temp2 t2 on t3.EventType = t2.EventType'


execute(@query);

SQL Pivot Issue, Need dynamic column names

1 Answers1