带日期时间的 SQL Server 动态数据透视表 [英] SQL Server dynamic Pivot with datetime

问题描述

我正在尝试对 join_date (datetime) 列进行透视并按年 + 月拆分它并按 mtype 分组，它必须是动态，因为日期会改变.

I'm trying to pivot join_date (datetime) column and split it by year + month and group by mtype and it must be dynamic as the date will change.

表格

+-------+------------------+
| Mtype |    join_date     |
+-------+------------------+
| A     | 28/05/2014 00:00 |
| B     | 04/11/2014 00:00 |
| C     | 23/03/2007 00:00 |
| D     | 04/09/2013 00:00 |
| E     | 26/11/2014 00:00 |
| F     | 24/07/2011 00:00 |
| G     | 28/05/2014 00:00 |
| A     | 20/06/2013 00:00 |
| B     | 04/11/2014 00:00 |
| C     | 20/06/2013 00:00 |
+-------+------------------+

想要的结果

+-------+---------+---------+---------+---------+---------+---------+--------+--------+--------+
| Mtype | 2015-03 | 2015-02 | 2015-01 | 2014-12 | 2014-11 | 2014-10 | 2014-9 | 2014-8 | 2014-7 |
+-------+---------+---------+---------+---------+---------+---------+--------+--------+--------+
| A     |      45 |       7 |      54 |     875 |      45 |       7 |     54 |    875 |     25 |
| B     |       7 |      78 |      78 |       7 |       7 |      78 |     78 |      7 |     78 |
| C     |     546 |       6 |      87 |       5 |      45 |       6 |     87 |      5 |     25 |
| D     |      54 |      78 |      54 |       8 |      54 |      78 |     54 |      7 |      7 |
| E     |      78 |      78 |       7 |      45 |      78 |      78 |      7 |     45 |      6 |
| F     |      46 |      54 |       6 |       8 |      46 |      54 |      6 |      8 |     75 |
| G     |      54 |      87 |      87 |       7 |      54 |      87 |     87 |     45 |      8 |
+-------+---------+---------+---------+---------+---------+---------+--------+--------+--------+

这是我对查询的看法，但我不知道如何让 Pivot 工作

This is my take on the query but I can't figure out how to get the Pivot working

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + LEFT(DATENAME(MONTH,join_date),3)+'/'+CAST(YEAR(join_date) AS
               VARCHAR(4)) + ']', 
              '[' + LEFT(DATENAME(MONTH,join_date),3)+'/'+CAST(YEAR(join_date) AS VARCHAR(4)) + ']')
               FROM    (SELECT DISTINCT join_date FROM MData T0) PV  
               ORDER BY join_date

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT Mtype, LEFT(DATENAME(MONTH,join_date),3)+''/''+CAST(YEAR(join_date) AS join_date  VARCHAR(4)) FROM MData T0
             ) x
             PIVOT 
             (
                 count(join_date)
                 join_date IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

推荐答案

这里是生成输出的sql代码.

Here is the sql code to generate the output.

DECLARE @cols NVARCHAR (MAX)
DECLARE @query NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(VARCHAR(7), join_date, 120) + ']', 
          '[' + CONVERT(VARCHAR(7), join_date, 120) + ']')
           FROM    (SELECT DISTINCT CONVERT(VARCHAR(7), join_date, 120) as join_date FROM Mdata) PV  
           ORDER BY join_date

SELECT @query = 'SELECT *
FROM
(
   SELECT Mtype, CONVERT(VARCHAR(7), join_date, 120) as join_date FROM Mdata
) AS t
PIVOT 
(
  COUNT(join_date) 
  FOR join_date IN( ' + @cols + ' )' +
') AS p ;'

EXEC SP_EXECUTESQL @query

这是结果的屏幕截图.

这篇关于带日期时间的 SQL Server 动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！