sql - 将日期值显示为列

Question

下面是我将日期值显示为列名的代码。
但除非以 Pivot 值编码，否则它不会获得下个月的新数据。
我应该如何动态地做到这一点？

SELECT *
FROM
(
SELECT REGION, STATUS, CAST((MONTH(TRANS_DATE))AS VARCHAR)+'-'+CAST(YEAR(TRANS_DATE)AS VARCHAR) AS TMONTH
FROM SOM_SAMPLE_MOVEMENT SM WHERE REGION IS NOT NULL AND TRANS_DATE IS NOT NULL AND STATUS='RECEIVED'

) T
PIVOT
(
COUNT(STATUS)
FOR TMONTH
IN ([11-2019],[12-2019],[1-2020],[2-2020],[3-2020])
)AS PVT

下面是示例输出。

REGION  11-2019 12-2019 1-2020  2-2020  3-2020
CEBU       3      10      15      0       0

Answer 1

您可以使用“真正的”动态 SQL:首先获取您的月份列表并使用 XML 路径将其存储到 nvarchar 变量中。然后使用构建的月份字符串将您的数据透视查询存储到一个新变量中并执行它。
有关详细信息，请参阅 fiddle :

SQL Fiddle

MS SQL Server 2017 架构设置 :

CREATE TABLE t1(
  REGION nvarchar(10), STATUS nvarchar(10), TRANS_DATE datetime
 );

查询 1 :

INSERT INTO T1 VALUES
('CEBU', 'Received', '2019-12-01'),
('CEBU', 'Received', '2019-12-01'),
('CEBU', 'Received', '2019-12-01'),
('CEBU', 'Received', '2020-01-01'),
('CEBU', 'Received', '2020-01-01'),
('CEBU', 'Received', '2020-01-02'),
('CEBU', 'Received', '2020-02-01'),
('CEBU', 'Received', '2020-03-01'),
('CEBU', 'Received', '2020-03-01'),
('CEBU', 'Received', '2020-05-01'),
('CEBU', 'Received', '2020-06-01')

Results :

查询 2 :

DECLARE @MonthList NVARCHAR(max) =(
SELECT  STUFF(( SELECT  ', ' + '['+TMONTH+']'
                FROM    ( SELECT DISTINCT CAST((MONTH(TRANS_DATE))AS VARCHAR)+'-'+CAST(YEAR(TRANS_DATE)AS VARCHAR) AS TMONTH
  FROM T1
                        ) x
              FOR
                XML PATH('')
              ), 1, 2, '') AllMonth
)

DECLARE @Stmt NVARCHAR(max) = 'SELECT *
FROM
(
SELECT REGION, STATUS, CAST((MONTH(TRANS_DATE))AS VARCHAR)+'+char(39)+'-'+char(39)+'+CAST(YEAR(TRANS_DATE)AS VARCHAR) AS TMONTH
FROM T1 SM WHERE REGION IS NOT NULL AND TRANS_DATE IS NOT NULL AND STATUS='+char(39)+'RECEIVED'+char(39)+'

) T
PIVOT
(
COUNT(STATUS)
FOR TMONTH
IN ('+@MonthList+')
)AS PVT'

EXEC(@stmt)

Results :

| REGION | 1-2020 | 12-2019 | 2-2020 | 3-2020 | 5-2020 | 6-2020 |
|--------|--------|---------|--------|--------|--------|--------|
|   CEBU |      3 |       3 |      1 |      2 |      1 |      1 |