有没有一种方法可以根据脚本运行的月份以可更改的顺序显示列

Question

想象一个非常简单的表，它存储产品的每月使用量：

CREATE TABLE [dbo].[ProductUsage](
[ProductId] [int] NOT NULL,
[Month1] [int] NOT NULL,
[Month2] [int] NOT NULL,
[Month3] [int] NOT NULL,
[Month4] [int] NOT NULL,
[Month5] [int] NOT NULL,
[Month6] [int] NOT NULL,
[Month7] [int] NOT NULL,
[Month8] [int] NOT NULL,
[Month9] [int] NOT NULL,
[Month10] [int] NOT NULL,
[Month11] [int] NOT NULL,
[Month12] [int] NOT NULL
) ON [PRIMARY]
GO

这存储了滚动的历史使用情况-因此，今天(3月6日(，Month3列包含2019年3月(2020年3月仍然存在(、Month2=2020年2月、Month1=2020年1月、Month2=2019年12月、Month11=2019年11月等的数据。

我需要从现在开始创建一个产品使用情况的报告，所以列需要有序产品ID，Month2，月1日，12月，11月。。。

等等。这需要新鲜，所以在四月份的订单是：产品ID，Month3，Month2，月1日，12月，11月。。。

有人能想出一个妙招吗？该报表将有一个今天日期的局部变量，从中可以计算列顺序。

非常感谢

Edward

Answer 1

在纯SQL中，不能动态更改列名，但可以动态更改与每列相关的值。

假设在原始表格中，Month1代表Jan，Month2代表February，Month3代表Mar等。

select * from ProductUsage

+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+
| ProductId | Month1 | Month2 | Month3 | Month4 | Month5 | Month6 | Month7 | Month8 | Month9 | Month10 | Month11 | Month12 |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+
|       111 |      1 |      2 |      3 |      4 |      5 |      6 |      7 |      8 |      9 |      10 |      11 |      12 |
|       222 |     21 |     22 |     23 |     24 |     25 |     26 |     27 |     28 |     29 |      30 |      31 |      32 |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+

-

使用建议的方法，列名将始终为Month1、Month2、，Month12(按顺序(，但如果例如当前月份是三月，则Month1将保持二月的值，Month2将保持一月的值，而Month3将保持十二月的值。

declare @month int = 3
select  * 
from   (select  ProductId, val, 'Month' + cast((12 - cast(substring(col,6,2) as tinyint) + @month - 1) % 12 + 1 as varchar(7))  as col
from    ProductUsage 
unpivot (val for col in (Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)) u
) pu_u 
pivot(max(val) for col in (Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)) p 

+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+
| ProductId | Month1 | Month2 | Month3 | Month4 | Month5 | Month6 | Month7 | Month8 | Month9 | Month10 | Month11 | Month12 |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+
|       111 |      2 |      1 |     12 |     11 |     10 |      9 |      8 |      7 |      6 |       5 |       4 |       3 |
|       222 |     22 |     21 |     32 |     31 |     30 |     29 |     28 |     27 |     26 |      25 |      24 |      23 |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+---------+---------+

SQL Fiddle

Answer 2

是的，很简单。

DECLARE @MesAct numeric
set @MesAct=3
DROP TABLE #ProductUsage
DROP TABLE #meses
CREATE TABLE #ProductUsage(
[ProductId] [int] NOT NULL,[Month1] [int] NOT NULL,[Month2] [int] NOT NULL,[Month3] [int] NOT NULL,
[Month4] [int] NOT NULL,[Month5] [int] NOT NULL,[Month6] [int] NOT NULL,[Month7] [int] NOT NULL,
[Month8] [int] NOT NULL,[Month9] [int] NOT NULL,[Month10] [int] NOT NULL,[Month11] [int] NOT NULL,[Month12] [int] NOT NULL) 
INSERT INTO #ProductUsage VALUES('10000','1','2','3','4','5','6','7','8','9','10','11','12')
INSERT INTO #ProductUsage VALUES('20000','12','23','34','45','56','67','78','89','91','102','113','124')
create table #meses (numMes numeric)
insert into #meses values(1)
insert into #meses values(2)
insert into #meses values(3)
insert into #meses values(4)
insert into #meses values(5)
insert into #meses values(6)
insert into #meses values(7)
insert into #meses values(8)
insert into #meses values(9)
insert into #meses values(10)
insert into #meses values(11)
insert into #meses values(12)
DECLARE  @cols  AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT  ',' + QUOTENAME('Month'+CAST(numMes AS VARCHAR)) 
FROM #meses 
Where numMes<@MesAct  
order by numMes desc 
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @cols = @cols+','+STUFF((SELECT  ',' + QUOTENAME('Month'+CAST(numMes AS VARCHAR)) 
FROM #meses 
Where numMes>=@MesAct  
order by numMes desc 
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')
------------------------------
DECLARE @Script nvarchar(max)
set @Script='select ProductId, '+@cols+' from #ProductUsage'
execute (@Script)

Answer 3

不是特别漂亮，但这可以做到：

select  ProductId,
case datepart(month,getdate()) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As LastMonth
,case datepart(month,Dateadd(month,-1,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As TwoMonthsAgo
,case datepart(month,Dateadd(month,-2,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As ThreeMonthsAgo
,case datepart(month,Dateadd(month,-3,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As FourMonthsAgo
,case datepart(month,Dateadd(month,-4,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As FiveMonthsAgo
,case datepart(month,Dateadd(month,-5,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As SixMonthsAgo
,case datepart(month,Dateadd(month,-6,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As SevenMonthsAgo
,case datepart(month,Dateadd(month,-7,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As EightMonthsAgo
,case datepart(month,Dateadd(month,-8,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As NineMonthsAgo
,case datepart(month,Dateadd(month,-9,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11  end As TenMonthsAgo
,case datepart(month,Dateadd(month,-10,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As ElevenMonthsAgo
,case datepart(month,Dateadd(month,-11,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As TwelveMonthsAgo
from ProductUsage

或者，如果你真的需要将月份的名称作为列标题，你可以这样做：

declare @month varchar(10)
set @month= DateName(month,DateAdd(month, -1,getDate()))
declare @TwoMonthsAgo varchar(10)
set @TwoMonthsAgo= DateName(month,DateAdd(month, -2,getDate()))
declare @Threemonthsago varchar(10)
set @Threemonthsago= DateName(month,DateAdd(month, -3,getDate()))
declare @fourmonthsago varchar(10)
set @fourmonthsago= DateName(month,DateAdd(month, -4,getDate()))
declare @fivemonthsago varchar(10)
set @fivemonthsago= DateName(month,DateAdd(month, -5,getDate()))
declare @sixmonthsago varchar(10)
set @sixmonthsago= DateName(month,DateAdd(month, -6,getDate()))
declare @sevenmonthsago varchar(10)
set @sevenmonthsago= DateName(month,DateAdd(month, -7,getDate()))
declare @eightmonthsago varchar(10)
set @eightmonthsago= DateName(month,DateAdd(month, -8,getDate()))
declare @ninemonthsago varchar(10)
set @ninemonthsago= DateName(month,DateAdd(month, -9,getDate()))
declare @tenmonthsago varchar(10)
set @tenmonthsago= DateName(month,DateAdd(month, -10,getDate()))
declare @elevenmonthsago varchar(10)
set @elevenmonthsago= DateName(month,DateAdd(month, -11,getDate()))
declare @twelvemonthsago varchar(10)
set @twelvemonthsago= DateName(month,DateAdd(month, -12,getDate()))
declare @query varchar(3800)
set @query =
'select ProductId, case datepart(month,getdate()) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11   end as '+@month+' '+
',case datepart(month,Dateadd(month,-1,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@TwoMonthsAgo+' '+
',case datepart(month,Dateadd(month,-2,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@ThreeMonthsAgo+' '+
',case datepart(month,Dateadd(month,-3,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@FourMonthsAgo+' '+
',case datepart(month,Dateadd(month,-4,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@FiveMonthsAgo+' '+
',case datepart(month,Dateadd(month,-5,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@SixMonthsAgo+' '+
',case datepart(month,Dateadd(month,-6,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@SevenMonthsAgo+' '+
',case datepart(month,Dateadd(month,-7,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@EightMonthsAgo+' '+
',case datepart(month,Dateadd(month,-8,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@NineMonthsAgo+' '+
',case datepart(month,Dateadd(month,-9,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end As '+@TenMonthsAgo+' '+
',case datepart(month,Dateadd(month,-10,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end AS '+@ElevenMonthsAgo+' '+
',case datepart(month,Dateadd(month,-11,getdate())) when 1 then Month12 when 2 then Month1 when 3 then  Month2 when 4 then  Month3 when 5 then  Month4 when 6 then  Month5 when 7 then  Month6 when 8 then  Month7 when 9 then  Month8 when 10 then  Month9 when 11 then  Month10 when 12 then  Month11 end AS '+@TwelveMonthsAgo+' '+
' From ProductUsage'
exec (@query)

对不起，我想我需要淋浴