PIVOT(透视转换)和UNPIVOT(逆透视转换)

一、原数据状态

二、手动写透视转换1

三、手动写透视转换2

四、PIVOT(透视转换)和UNPIVOT(逆透视转换)详细使用

• 使用标准SQL进行透视转换和逆视转换

--行列转换
create table #demoOrders
(
   id int primary key identity(1,1),
   CompanyName nvarchar(50),
   ProductID int,
   ProductName nvarchar(50)
)
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司1','1','产品1')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司1','2','产品2')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司2','2','产品2')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司2','3','产品3')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司3','3','产品3')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司4','3','产品3')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司5','4','产品4')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司6','4','产品4')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司6','5','产品5')

select * from #demoOrders

　　

　　透视转换的标准SQL解决方案以一种非常直接的方式来处理转换过程中涉及的三个阶段：
　　　　1、分组阶段用group by 子句实现
　　　　2、扩展阶段通过在select子句中为每个目标列指定case表达式来实现，这需要事先知道每个扩展元素的取值，并为每个值指定一个单独的case表达式。
　　　　3、聚合阶段通过为每个case表达式的结果应用相关的聚合函数来实现。

　　解题思维步骤：

　　1.先找到为行列转换的数据，分组查看数据试试：　

select CompanyName,ProductName,count(*) as num from #demoOrders
group by ProductName,CompanyName order by CompanyName

　　

　　2.分组阶段:用group by 子句以行作为分组条件,获取行数据

select CompanyName
from (
    select CompanyName,ProductName,COUNT(*)as num from #demoOrders group by ProductName,CompanyName
) T 
group by CompanyName

　　

　　3.扩展阶段:找到列的数据,为每个目标列指定case表达式;聚合阶段通过为每个case表达式的结果应用相关的聚合函数来实现

select CompanyName,
sum(case when ProductName='产品1' then num else 0 end)[产品1],
sum(case when ProductName='产品2' then num else 0 end)[产品2],
sum(case when ProductName='产品3' then num else 0 end)[产品3],
sum(case when ProductName='产品4' then num else 0 end)[产品4],
sum(case when ProductName='产品5' then num else 0 end)[产品5] 
from (
    select CompanyName,ProductName,COUNT(*)as num from #demoOrders group by ProductName,CompanyName
) T 
group by CompanyName

--以下是分页存储过程，看看拼接sql语句字符串和执行的过程，然后把思路打开一下试试
declare @sql nvarchar(1000)
set @sql='select CompanyName,'--开始设置语句
--------动态生成语句begin(开始转成列)-----
select @sql=@sql+'sum(case when ProductName='''+ProductName+''' then num else 0 end)['+ProductName+'],'
from (select distinct top 100 percent ProductName from #demoOrders order by ProductName)a
--------动态生成语句  end--------------------
print @sql
set @sql =left(@sql,len(@sql)-1)+' from (select CompanyName,ProductName,COUNT(*)as num   from #demoOrders group by ProductName,CompanyName)a group by CompanyName'
print @sql    --打印输出最终执行的SQL
exec(@sql)    --执行SQL字符串

　　　

　　

　　逆透视转换的标准SQL解决方案要实现三个逻辑处理阶段：
　　　　1、生成副本：根据来源表的每一行生成多个副本(为需要逆透视的每个列生成一个副本)；用cross join(交叉联接)来生成每一行的多个副本
　　　　2、提取元素
　　　　3、删除不相关的交叉

--逆视数据
select CompanyName,
sum(case when ProductName='产品1' then num else 0 end)[产品1],
sum(case when ProductName='产品2' then num else 0 end)[产品2],
sum(case when ProductName='产品3' then num else 0 end)[产品3],
sum(case when ProductName='产品4' then num else 0 end)[产品4],
sum(case when ProductName='产品5' then num else 0 end)[产品5] 
into #unpivotDemo
from (
    select CompanyName,ProductName,COUNT(*)as num from #demoOrders group by ProductName,CompanyName
) a group by CompanyName

　　1、在#unpivotDemo表和每行ProductName之间进行交叉联接

select * from #unpivotDemo 
cross join 
(values('产品1'),('产品2'),('产品3'),('产品4'),('产品5')) as #unpivotDemo2(ProductName)
--或：
select * from #unpivotDemo 
cross join 
(
    select '产品1' as ProductName 
    union all
    select '产品2'
    union all
    select '产品3'
    union all
    select '产品4'
    union all
    select '产品5'
) as #unpivotDemo2

　　

　　2.1、生成一个数据列，由它返回与当前副本所代表的产品相对应的列值　　

select *,
case ProductName
    when '产品1' then 产品1
    when '产品2' then 产品2
    when '产品3' then 产品3
    when '产品4' then 产品4
    when '产品5' then 产品5
end as num
from #unpivotDemo 
cross join (values('产品1'),('产品2'),('产品3'),('产品4'),('产品5')) as #unpivotDemo2(ProductName)
--或：
select *,
case ProductName
    when '产品1' then 产品1
    when '产品2' then 产品2
    when '产品3' then 产品3
    when '产品4' then 产品4
    when '产品5' then 产品5
end as num
from #unpivotDemo 
cross join 
(
    select '产品1' as ProductName 
    union all
    select '产品2'
    union all
    select '产品3'
    union all
    select '产品4'
    union all
    select '产品5'
) as #unpivotDemo2

　　

　　2.2、提取所需的数据列　　

select CompanyName,ProductName,
case ProductName
    when '产品1' then 产品1
    when '产品2' then 产品2
    when '产品3' then 产品3
    when '产品4' then 产品4
    when '产品5' then 产品5
end as num
from #unpivotDemo 
cross join (values('产品1'),('产品2'),('产品3'),('产品4'),('产品5')) as #unpivotDemo2(ProductName)
--或：
select CompanyName,ProductName,
case ProductName
    when '产品1' then 产品1
    when '产品2' then 产品2
    when '产品3' then 产品3
    when '产品4' then 产品4
    when '产品5' then 产品5
end as num
from #unpivotDemo 
cross join 
(
    select '产品1' as ProductName 
    union all
    select '产品2'
    union all
    select '产品3'
    union all
    select '产品4'
    union all
    select '产品5'
) as #unpivotDemo2

　　

　　3、0值与NULL值代表不相关的交叉，为了删除不相关的交叉，在外部查询中过滤掉0值与NULL值

select * from
(
    select CompanyName,ProductName,
    case ProductName
        when '产品1' then 产品1
        when '产品2' then 产品2
        when '产品3' then 产品3
        when '产品4' then 产品4
        when '产品5' then 产品5
    end as num
    from #unpivotDemo 
    cross join (values('产品1'),('产品2'),('产品3'),('产品4'),('产品5')) as #unpivotDemo2(ProductName)
) as T
where num is not null and num <> 0
--或：
select * from
(
    select CompanyName,ProductName,
    case ProductName
        when '产品1' then 产品1
        when '产品2' then 产品2
        when '产品3' then 产品3
        when '产品4' then 产品4
        when '产品5' then 产品5
    end as num
    from #unpivotDemo 
    cross join 
    (
        select '产品1' as ProductName 
        union all
        select '产品2'
        union all
        select '产品3'
        union all
        select '产品4'
        union all
        select '产品5'
    ) as #unpivotDemo2
) as T
where num is not null and num <> 0

　　

• 使用T-SQL PIVOT透视转换和UNPIVOT逆透视转换

　　pivot的使用

select CompanyName,[产品1] as 产品1,[产品2] as 产品2,[产品3] as 产品3,[产品4] as 产品4,[产品5] as 产品5
from 
(
    --表表达式作为pivot输入表,仅仅返回透视中用到的列
    select CompanyName,ProductName,count(*) as num from #demoOrders
    group by ProductName,CompanyName
) as sourceTable    --分组是隐含的，对表中除掉聚合和条件的列进行分组
pivot
(
    sum(num)    --聚合函数
    for ProductName in([产品1],[产品2],[产品3],[产品4],[产品5])    --准备做列名
) as PivotTable

create table #demotable
(
   id int primary key identity(1,1),
   orderMonth int ,
   subTotal decimal(18,2)
)
insert into #demotable (orderMonth,subTotal) values(5,100.00)
insert into #demotable (orderMonth,subTotal) values(6,100.00)
insert into #demotable (orderMonth,subTotal) values(5,200.00)
insert into #demotable (orderMonth,subTotal) values(6,200.00)
insert into #demotable (orderMonth,subTotal) values(7,100.00)
select * from #demotable

--方式一
select id,[5] as 五月,[6] as 六月,[7] as 七月
from 
#demotable    --基础表作为pivot输入表
pivot
(
    sum(#demotable.subTotal) for #demotable.orderMonth in([5],[6],[7])
) as PivotTable
--方式二(推荐使用表表达式作为pivot的输入表，不要对基础表进行操作)：
select id,[5] as 五月,[6] as 六月,[7] as 七月
from 
(
    --表表达式作为pivot输入表,仅仅返回透视中用到的列
    select id,orderMonth,subTotal from #demotable
) as sourceTable    --分组是隐含的，对表中除掉聚合和条件的列进行分组
pivot
(
    sum(subTotal)    --聚合函数
    for orderMonth in([5],[6],[7])    --准备做列名
) as PivotTable
drop table #demotable

　　

　　unpivot的使用

create table #demotable2 
(
    id int,
    五月 int,
    六月 int,
    七月 int
)
insert into #demotable2 values (1,100,100,0);
insert into #demotable2 values (2,200,200,200);
insert into #demotable2 values (3,800,0,0);
select * from #demotable2

--执行UNPIVOT
select id,orderMonth,subTotal
FROM 
#demotable2
unpivot
(
    subTotal for orderMonth in(五月,六月,七月)
)AS UnpivotTable
drop table #demotable2

　　

练习：

create table #testtable
(
   id int primary key identity(1,1),
   t_year int ,
   t_month int,
   t_amount decimal(18,1)
)

insert into #testtable (t_year,t_month,t_amount) values(1991,1,1.1)
insert into #testtable (t_year,t_month,t_amount) values(1991,2,1.2)
insert into #testtable (t_year,t_month,t_amount) values(1991,3,1.3)

insert into #testtable (t_year,t_month,t_amount) values(1992,1,2.1)
insert into #testtable (t_year,t_month,t_amount) values(1992,2,2.2)
insert into #testtable (t_year,t_month,t_amount) values(1992,3,2.3)
--drop table #testtable
select * from #testtable

--//想要的结果
--year m1   m2   m3
--1991 1.1  1.2  1.3
--1992 2.1  2.2  2.3

select max(t_year) as [year],max([1]) as m1,max([2]) as m2,max([3]) as m3
from #testtable
pivot 
(
  max(t_amount) for t_month in([1],[2],[3])
) as PivotTable
group by t_year

select t_amount,ColumnName,YearAndMonth
from #testtable
unpivot
(
    YearAndMonth for ColumnName in(t_year,t_month)
) as UnpivotTable

--行列转换
--解题思维步骤：
--1.先找到为行列转换的数据，查看数据试试：
select t_year,t_month,t_amount from #testtable
--2.找到列的数据
select
(case when t_month=1 then t_amount else 0 end)[m1],
(case when t_month=2 then t_amount else 0 end)[m2],
(case when t_month=3 then t_amount else 0 end)[m3]
from #testtable
--3.以行作为分组条件,获取行数据;两者结合起来，答案：
select t_year,
max(case when t_month=1 then t_amount else 0 end)[m1],
max(case when t_month=2 then t_amount else 0 end)[m2],
max(case when t_month=3 then t_amount else 0 end)[m3]
from #testtable
group by t_year

--------------------以下是sql语句字符串和执行的过程------------------------
declare @sql nvarchar(1000)
set @sql='select t_year,'
--------动态生成列 begin--------
select @sql=@sql+'max(case when t_month='+convert(nvarchar(20),t_month)+' then t_amount else 0 end)[m'+str(t_month,1)+'],'
from (select distinct top 100 percent t_month from #testtable order by t_month) T
print @sql
--------动态生成列 end--------
set @sql=left(@sql,len(@sql)-1)+' from #testtable group by t_year'
print @sql
exec(@sql)