在动态数据透视表中对月份名称进行排序 [英] Sorting month name in dynamic pivot

问题描述

我使用以下查询生成了一个动态数据透视表，但列 (monthyear) 有效按字母顺序排列，但我希望它们按时间顺序排列月年列是使用 SQL Server 2014 中的函数导出的

I produced a dynamic pivot with the following query which work but the column (monthyear) are in alphabetical order but I want them in chronological order The monthyear column is derived using a function in SQL Server 2014

CREATE TABLE ##MyTable (Num VARCHAR(10),    StartDate DATE, [Types] VARCHAR(10))
INSERT INTO ##MyTable VALUES
('AA1','2016-01-01', 'Type1'),('AA2','2017-01-04', 'Type1'),('AA3','2016-01-04', 'Type1'),('AA4','2017-01-01', 'Type2'),
('AA5','2017-01-10', 'Type3'),('AA6','2016-01-02', 'Type1'),('AA7','2017-01-05', 'Type1'),('AA8','2016-01-12', 'Type1'),
('AA9','2016-01-06', 'Type1'),('AA10','2016-01-10', 'Type3'),('AA11','2017-01-11', 'Type1'),('AA12','2016-01-09', 'Type2'),
('AA13','2016-08-06', 'Type3'),('AA14','2017-01-02', 'Type1'),('AA15','2016-01-05', 'Type1'),('AA16','2017-01-07', 'Type1'),
('AA17','2016-01-04', 'Type1'),('AA18','2017-01-03', 'Type3'),('AA19','2017-01-01', 'Type1'),('AA20','2016-01-10', 'Type2'),
('AA21','2018-01-02', 'Type3'),('AA22','2017-01-10', 'Type1'),('AA23','2017-01-11', 'Type1'),('AA24','2017-01-12', 'Type1'),
('AA25','2017-01-09', 'Type1'),('AA26','2017-01-03', 'Type3'),('AA27','2016-01-07', 'Type1'),('AA28','2017-01-03', 'Type3'),
('AA29','2016-01-09', 'Type3'),('AA30','2017-10-12', 'Type1'),('AA31','2016-01-08', 'Type1'),('AA32','2017-01-10', 'Type1'),
('AA33','2016-01-04', 'Type1'),('AA34','2016-01-03', 'Type1'),('AA35','2018-01-01', 'Type3'),('AA36','2016-01-12', 'Type3'),
('AA37','2017-01-12', 'Type1'),('AA38','2016-01-05', 'Type1'),('AA39','2017-01-01', 'Type1'),('AA40','2017-01-12', 'Type3'),
('AA41','2017-01-07', 'Type1'),('AA42','2017-01-04', 'Type3'),('AA43','2018-01-03', 'Type1'),('AA44','2016-01-08', 'Type1'),
('AA45','2016-09-10', 'Type1'),('AA46','2016-01-11', 'Type3'),('AA47','2017-01-10', 'Type1'),('AA48','2017-01-08', 'Type1'),
('AA49','2017-01-08', 'Type1'),('AA50','2016-01-06', 'Type3'),('AA51','2016-02-08', 'Type3'),('AA52','2017-01-02', 'Type3'),
('AA53','2018-01-01', 'Type3'),('AA54','2016-01-05', 'Type3'),('AA55','2018-01-02', 'Type1'),('AA56','2018-01-01', 'Type1'),
('AA57','2017-01-10', 'Type1'),('AA58','2017-01-11', 'Type3'),('AA59','2018-01-03', 'Type3'),('AA60','2017-01-05', 'Type1'),
('AA61','2016-01-10', 'Type3'),('AA62','2017-01-08', 'Type3'),('AA63','2016-01-06', 'Type2'),('AA64','2017-01-05', 'Type3'),
('AA65','2018-01-01', 'Type3'),('AA66','2017-02-03', 'Type1'),('AA67','2016-01-12', 'Type1'),('AA68','2016-01-11', 'Type3'),
('AA69','2016-01-09', 'Type3'),('AA70','2017-01-12', 'Type2'),('AA71','2016-01-08', 'Type3'),('AA72','2016-01-10', 'Type1'),
('AA73','2017-01-05', 'Type3'),('AA74','2016-01-02', 'Type3'),('AA75','2016-01-12', 'Type3'),('AA76','2016-01-02', 'Type1'),
('AA77','2017-02-08', 'Type1'),('AA78','2016-01-12', 'Type3'),('AA79','2017-01-04', 'Type1'),('AA80','2018-01-01', 'Type2'),
('AA81','2016-01-08', 'Type3'),('AA82','2017-01-11', 'Type1'),('AA83','2017-01-05', 'Type1');

--  --  PIVOT
SELECT 
                Num,
                [Types],
                StartDate,
                FORMAT(StartDate,'MMM-yy')AS MonthYear
        INTO ##MyTable2
FROM ##MyTable
-------------------------------------------------------------------------------
DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(MonthYear) 
            FROM ##MyTable2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = ' SELECT * FROM (
                SELECT
                Num,
                [Types],
                MonthYear
                FROM ##MyTable2) AS PV
PIVOT
(
COUNT(Num) FOR [MonthYear] IN (' + @cols + ')
) AS PV1'

EXECUTE (@query);

DROP TABLE ##MyTable;
DROP TABLE ##MyTable2;

电流输出

Types   Aug-16  Feb-16  Feb-17  Jan-16  Jan-17  Jan-18  Oct-17  Sep-16
Type1   0          0      2       16      22       3      1       1
Type2   0          0      0        3       2       1      0       0
Type3   1          1      0       14      11       5      0       0

期望的输出

Types   Jan-16  Feb-16  Aug-16  Sep-16  Jan-17  Feb-17  Oct-17  Jan-18
Type1   16        0       0      1        22       2       1       3
Type2   3         0       0      0         2       0       0       1
Type3   14        1       1      0        11       0       0       5

有没有办法在sql中实现?

Is there a way this can be achieved in sql ?

推荐答案

我会使用以下方法按升序对月份进行排序

I'd use the following approach to sort the months in ascending order

演示

SELECT 
            Num,
            [Types],
            StartDate,
            FORMAT(StartDate,'MMM-yy')AS MonthYear,
            CONVERT(INT,REPLACE(CONVERT(VARCHAR(7),StartDate),'-','')) MonthYearSort
    INTO ##MyTable2
    FROM ##MyTable

DECLARE @cols AS NVARCHAR(MAX)='',
        @query  AS NVARCHAR(MAX);


WITH T AS
(
SELECT TOP 100 PERCENT QUOTENAME(MonthYear) MonthYear  , MonthYearSort
FROM ##MyTable2
GROUP BY QUOTENAME(MonthYear)  , MonthYearSort
ORDER BY MonthYearSort
)

SELECT @cols += ','+ MonthYear 
FROM T
ORDER BY MonthYearSort
SET @cols = STUFF(@Cols ,1,1,'')


set @query = ' SELECT [Types], '+@cols+' FROM (
                SELECT
                Num,
                [Types],
                MonthYear
                FROM ##MyTable2) AS PV
PIVOT
(
COUNT(Num) FOR [MonthYear] IN (' + @cols + ')
) AS PV1

EXECUTE (@query);

这篇关于在动态数据透视表中对月份名称进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！