使用多个计数/总和跨多个字段进行透视

Philip 发表于 Dev

菲利普

我在表格中有以下结果。

MonthYear   MonthNumber  YearDate  Clients NonClients
-------------------------------------------------
Feb-20      2            2020      46      0
Jan-20      1            2020      21      0
Mar-20      3            2020      37      0

SQL

CREATE TABLE [dbo].[Results](
    [MonthYear] [nvarchar](35) NULL,
    [MOnthNumber] [int] NULL,
    [YearDate] [int] NULL,
    [Clients] [int] NULL,
    [NonClients] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Results] ([MonthYear], [MOnthNumber], [YearDate], [Clients], [NonClients]) VALUES (N'February 2020', 2, 2020, 46, 0)
GO
INSERT [dbo].[Results] ([MonthYear], [MOnthNumber], [YearDate], [Clients], [NonClients]) VALUES (N'January 2020', 1, 2020, 21, 2)
GO
INSERT [dbo].[Results] ([MonthYear], [MOnthNumber], [YearDate], [Clients], [NonClients]) VALUES (N'March 2020', 3, 2020, 37, 0)
GO

如何按日期顺序将其转换为以下内容，并且 MonthYear 灵活，其中可以包含任意数量的月/年？

              Jan-20   Feb-20   Mar-20
------------------------------------------
Non Clients   2        0        0
Clients       21       46       37

到目前为止，我有以下内容，但无法完成：

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = ''

-- select the column headers
SELECT 
    @columns+=QUOTENAME(MonthYear) + ','
FROM 
    Results R
order by
    YearDate desc,
    MonthNumber desc

-- construct dynamic SQL
SET @sql ='
SELECT * FROM       
(
    select * from #Results
) t 
PIVOT(
    Sum(Clients) 
    FOR MonthYear IN ('+ @columns +')
) AS pivot_table
'

-- execute the dynamic SQL
EXECUTE sp_executesql @sql

包

您将需要Unpiovt和Pivot才能获得所需的结果。我已添加到您的代码中。

--unpivoting
drop table if exists #results
select MonthYear, MOnthNumber, YearDate, col, val, dense_rank() over (order by YearDate desc, MonthNumber) rn   --to preserve the order
into #results
from
(
    select MonthYear, MOnthNumber, YearDate, Clients, NonClients
    from Results
)t
unpivot
(
    val for col in (Clients,NonClients)
)upvt

DECLARE 
    @columns NVARCHAR(MAX), 
    @sql     NVARCHAR(MAX) = ''

-- select the column headers
SELECT 
    @columns = COALESCE(@columns + ', ', '') + QUOTENAME(MonthYear)
FROM 
    (select distinct MonthYear, rn from #results)R
order by rn

-- construct dynamic SQL
SET @sql ='
SELECT col as C_or_NC, '+@columns+' FROM       
(
    select MonthYear, col, val from #Results
) t 
PIVOT(
    Sum(val) 
    FOR MonthYear IN ('+ @columns +')
) AS pivot_table
order by 1 desc'

print @sql

-- execute the dynamic SQL
EXECUTE sp_executesql @sql

请在此处查看演示。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。