sql server 2008 - T-SQL Pivot/Unpivot(Transpose) Column Headers Needed as Data Rows

Question

I'm working on a T-SQL issue where I needed to Transponse Rows into Columns and using UNPIVOT and PIVOT together per a post at Simple way to transpose columns and rows in Sql?

No problem. It does Exactly what I want with the row/column manipulation. However what I would REALLY like to do is to get the values used for the column headers to become yet another row in the results.

My abbreviated code is:

SELECT * 
FROM   (SELECT fiscalyear, 
              Sum(totalrecords)  AS TotalRecords 
        FROM   dbo.tbleirstatisticsoverviewsummary 
        WHERE  fiscalquarter = 'ALL' 
               AND branchcode = 'ALL' 
        GROUP  BY fiscalyear, 
                  fiscalquarter, 
                  branchcode) AS p 
       UNPIVOT (value 
               FOR colname IN ( totalrecords )) AS unpvt 
       PIVOT   (Max(value) For  FiscalYear IN ([2012],[2013],[ALL])) as  p  

What it renders is:
colname         2012 2013 ALL
TotalRecords 421   227   648

Where the first line is column headers.

Any thoughts on how I could get the column headers to be data rows?

Adding some sample Raw Data fiscalyear TotalRecords 2012 421 2013 227 ALL 648

Answer 1

There are a few confusing things that you are doing.

First, typically you will unpivot multiple columns. Right now, you are are unpivoting one column and it seems like you are doing it just to rename the column?

Second, you are aggregating the data twice, the PIVOT should be able to handle the aggregation using SUM().

Third, it is not exactly clear on why you need the column headers as a row, what will you want the column headers to be called?

Based on your sample data you should be able to just apply the PIVOT function:

select 'TotalRecords' TotalRecords, 
  [2012],
  [2013],
  [All]
from tbleirstatisticsoverviewsummary
pivot
(
  sum(totalrecords)
  for FiscalYear IN ([2012],[2013],[ALL])
) p;

See SQL Fiddle with Demo. Then if you want a row with the columns headers, then you can use a UNION ALL:

select 'colname' col1, 
  2012 col2, 
  2013 col3, 
  'All' col4
union all
select 'TotalRecords' TotalRecords, 
  [2012],
  [2013],
  [All] = cast([all] as varchar(10))
from tbleirstatisticsoverviewsummary
pivot
(
  sum(totalrecords)
  for FiscalYear IN ([2012],[2013],[ALL])
) p;

See SQL Fiddle with Demo