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

score 1 · Answer 1 · answered Aug 01 '13 at 17:45

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

Thanks for the response. For your points 1) I left off the multiple other columns...to save space (I'll use SQL Fiddle in future have never done that). 2) I'll try it out 3) why do i want them...because basically they feed something else...where the column headers (FIscal years in this case) need to be in the data result...not as column names. Thanks again. — JTSOne, Aug 01 '13 at 17:51
@todd If you are using more than one column in the unpivot then it is important to include all necessary details to answer the question properly. Providing minimal details makes it very difficult to answer your question. I would suggest editing the sql fiddle with all of the needed details and data. — Taryn, Aug 01 '13 at 17:59

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

1 Answers1

Linked