pivot行转列与unpivot列转行

create table #temp(id int,dataType int,val int,price decimal(10,2));
--插入数据
insert into #temp(id,dataType,val,price)
select 1,1,100,20.5
 union all
select 1,2,200,30.6
 union all
select 1,3,300,40.7
--查询数据
select id,dataType,val,price 
  from #temp;
--将一行多列转换成一列
select id, value, valueType + convert(varchar(1),dataType) valueType
  from (select id,dataType,convert(decimal(10,2),val) val,convert(decimal(10,2),price) cd 
                           --注意：要转换的列要保持类型一致，不然会报错
          from #temp
        ) x
unpivot (value for valueType in (val,cd)) y

效果如图：

--将多行转换成多列
select id,val1,cd1,val2,cd2,val3,cd3
  from (select id,value,valueType+CONVERT(varchar(1),dataType) valueType --注意：不同类型列拼接，注意转换
          from (select id,dataType,convert(decimal(10,2),val) val,convert(decimal(10,2),price) cd 
                                   --注意：要转换的列要保持类型一致，不然会报错
                  from #temp
                ) x
        unpivot (value for valueType in (val,cd)) y --注意：如果in()中为数字，用中括号([])括起来。例如：in ([001],[002],[003])
                                                    --SO问题来了：怎样调用字段？
                                --调用字段时，同样采用[001],[002],[003]字段，SQLServer中会把中括号([])解析掉
                                --如果比较列标题是否一致时，比如：[001]标题解析为：001，它是否于正常的001一样呢？
                                --答案：不一样。建议给这些特殊字段标题起个别名
        ) m
  pivot (max(value) for valueType in (val1,cd1,val2,cd2,val3,cd3)) n;

效果如图：

/* 避免转换时数据类型报错，建议临时表直接都存为varchar类型 */
create table #a(Id int identity(1,1), Bb varchar(50), Tsgs varchar(50), Zz varchar(50))
insert into #a 
select distinct case Bb when '00f45749-7706-47cf-b00a-bc47c5d87eb9' then '甲班' when '36d7b28f-f375-49df-907f-9f0385e3cc74' then '乙班' when '36d7b28f-f375-49df-907f-9f0385e3cc74' then '丙班' when '9745bc84-73b3-4736-8c5d-d0318d1e3708' then '丁班' end Bb, Tsgs, Zz from MES_Prcj_Jsltczjl where RsjhId = '3063864'

select * from #a

--将一行多列转换为一列
select value, valueType + convert(varchar(1),y.Id) valueType from (select Id, Bb, Tsgs, Zz from #a) x 
unpivot (value for valueType in (Bb, Tsgs, Zz))y

--将多列多行转换为一行
select Bb1, Tsgs1, Zz1, Bb2, Tsgs2, Zz2 from 
(
  select value, valueType + convert(varchar(1),y.Id) valueType from (select Id, Bb, Tsgs, Zz from #a) x 
unpivot (value for valueType in (Bb, Tsgs, Zz))y
) m
pivot (max(value) for valueType in(Bb1, Tsgs1, Zz1, Bb2, Tsgs2, Zz2)) n

效果如图：

 

pivot操作还可以使用聚合操作，如sum(),avg(),max().......在for之前使用聚合函数

unpviot操作涉及到以下三个逻辑处理阶段。

1，生成副本

2，提取元素

3，删除带有NULL的行

 

转载于:https://www.cnblogs.com/Java-125/p/11235911.html