SQL数据透视问题，需要动态列名

Question

好的。因此，我们需要通过下面的代码来获得输出。我的大部分代码来自：使用'；数据透视'；在SQL Server中。这给了我年份的动态列名，然后统计了每年的事件。我需要（或者至少希望）将列名格式化为2014财年总计等，而不仅仅是2014年。因此创建了另一个列名称列表，用作输出名称。我想，在进行透视时，您应该为新列提供自定义名称。我不确定我需要做什么。

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

Answer 1

在temp2 cte中同时使用@colValues。。然后在你的最终选择中使用这个。

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,'')

应该给你这样的东西。。

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