一、原数据状态
二、手动写透视转换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')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司1','','产品2')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司2','','产品2')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司2','','产品3')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司3','','产品3')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司4','','产品3')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司5','','产品4')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司6','','产品4')
insert into #demoOrders (CompanyName,ProductID,ProductName) values('公司6','','产品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,[] as 五月,[] as 六月,[] as 七月
from
#demotable --基础表作为pivot输入表
pivot
(
sum(#demotable.subTotal) for #demotable.orderMonth in([],[],[])
) as PivotTable
--方式二(推荐使用表表达式作为pivot的输入表，不要对基础表进行操作)：
select id,[] as 五月,[] as 六月,[] as 七月
from
(
--表表达式作为pivot输入表,仅仅返回透视中用到的列
select id,orderMonth,subTotal from #demotable
) as sourceTable --分组是隐含的，对表中除掉聚合和条件的列进行分组
pivot
(
sum(subTotal) --聚合函数
for orderMonth in([],[],[]) --准备做列名
) 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([]) as m1,max([]) as m2,max([]) as m3
from #testtable
pivot
(
max(t_amount) for t_month in([],[],[])
) 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)