By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU1) (KB4527376) - 15.0.4003.23 (X64) Dec 6 2019 14:53:33 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
drop table if exists dbo.Results
CREATE TABLE [dbo].[Results](
[MonthYear] [nvarchar](35) NULL,
[MOnthNumber] [int] NULL,
[YearDate] [int] NULL,
[Clients] [int] NULL,
[NonClients] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[Results] ([MonthYear], [MOnthNumber], [YearDate], [Clients], [NonClients]) VALUES (N'February 2020', 2, 2020, 46, 0)
INSERT [dbo].[Results] ([MonthYear], [MOnthNumber], [YearDate], [Clients], [NonClients]) VALUES (N'January 2020', 1, 2020, 21, 2)
INSERT [dbo].[Results] ([MonthYear], [MOnthNumber], [YearDate], [Clients], [NonClients]) VALUES (N'March 2020', 3, 2020, 37, 0)
--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
| C_or_NC | January 2020 | February 2020 | March 2020 |
|---|---|---|---|
| NonClients | 2 | 0 | 0 |
| Clients | 21 | 46 | 37 |