is it possible to have 2 nested pivot functions in TSQL

Question

I am fighting with formatting using TSQL code. I thought that maybe I can achieve what I want using PIVOT function (sql 2012) but I think I would need to nest 2 Pivots in the same query and I am not sure if that is possible.

my table looks like this:Raw data and Ideally I would like to export to a .csv format like this Formatted output .csv file

I have tried using a pivot with the following code `select * from ( select distinct monthnum ,monthname -- ,Urban_rural ,performance ,Jurisdiction

from ##cte
 )as  src
 pivot 
    (
    avg(performance)
    for jurisdiction in ([North],[south],[east],[west])
    )piv;` but its only partly getting me where I wanted to be. [Pivot results][3].  So the questions I have are:  How do i get my output file to look like the sample using SSIS or TSQL.  Can you nest Pivot functions (or should you?)

Cheers!

Answer 1

Lakta's suggestion of using a pivot table with Unions returned the results I was looking for.

To be frank, I would not recommend nesting the pivots. What you could do is making a staging table (ex temporal or cte) in which you "pre-aggregate" your values in such a way, that they have a common pivot-able key: North_Urban, North_Rural, North_All, etc. That can easily be done like:

SELECT monthnum,
       monthname,
       Urban_rural + '_' + Jurisdiction Urban_rural_Jurisdiction
FROM ##cte union select monthnum, monthname, Urban_rural + '_All' Urban_rural_Jurisdiction from ##cte