Pivoting across multiple Fields with multiple Count/Sum

I have the below results within a table.

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

The 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

How can I turn this into the following with it in date order, and the MonthYear being flexible where it could contain any number of months/years?

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

I’ve got the following so far, but can’t manage to finish it off:

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

Answer

You would need both Unpiovt and Pivot to achieve the desired result. I’ve added to your code.

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

please see the demo here.