时间戳

首页 > 解决方案 > 将日期值显示为列

sql - 将日期值显示为列

问题描述

下面是我将日期值显示为列名的代码。但它不会得到下个月的新数据，除非用 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

标签： sqlsql-server

解决方案

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

SQL小提琴

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

结果：

查询 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)

结果：

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

推荐阅读