数据行需要T-SQL数据透视/取消透视(转置)列标题 [英] T-SQL Pivot/Unpivot(Transpose) Column Headers Needed as Data Rows

问题描述

我正在处理一个T-SQL问题，我需要将行转换为列，并根据

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.

我的缩写代码是:

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  

它呈现的是:
姓氏          2012 2013 ALL
TotalRecords  421   227    648

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

第一行是列标题.

关于如何使列标题成为数据行的任何想法?

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

添加一些原始数据样本 会计年度总记录 2012 421 2013 227 全部648

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

推荐答案

您正在做一些令人困惑的事情.

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?

第二，您要汇总两次数据，PIVOT应该能够使用SUM()处理汇总.

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?

根据示例数据，您应该只可以应用PIVOT功能:

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;

请参见带演示的SQL小提琴.然后，如果您想使用带有列标题的行，则可以使用UNION ALL:

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;

请参见带有演示的SQL小提琴

这篇关于数据行需要T-SQL数据透视/取消透视(转置)列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！