sql中pivot函数(行转列),unpivot(列转行)

1、create table DailyIncome(VendorId nvarchar(50), IncomeDay nvarchar(50), IncomeAmount int,FacName nvarchar(50))
2、插入数据

insert into DailyIncome values (‘SPIKE’, ‘FRI’, 100,’mixue’)
insert into DailyIncome values (‘SPIKE’, ‘MON’, 300,’mixue’)
insert into DailyIncome values (‘FREDS’, ‘SUN’, 400,’naike’)
insert into DailyIncome values (‘SPIKE’, ‘WED’, 500,’naike’)
insert into DailyIncome values (‘SPIKE’, ‘TUE’, 200,’naike’)
insert into DailyIncome values (‘JOHNS’, ‘WED’, 900,’naike’)
insert into DailyIncome values (‘SPIKE’, ‘FRI’, 100,’adi’)
insert into DailyIncome values (‘JOHNS’, ‘MON’, 300,’adi’)
insert into DailyIncome values (‘SPIKE’, ‘SUN’, 400,’adi’)
insert into DailyIncome values (‘JOHNS’, ‘FRI’, 300,’adi’)
insert into DailyIncome values (‘FREDS’, ‘TUE’, 500,’nong’)
insert into DailyIncome values (‘FREDS’, ‘TUE’, 200,’nong’)
insert into DailyIncome values (‘SPIKE’, ‘MON’, 900,’nong’)
insert into DailyIncome values (‘FREDS’, ‘FRI’, 900,’nong’)
insert into DailyIncome values (‘FREDS’, ‘MON’, 500,’nong’)
insert into DailyIncome values (‘JOHNS’, ‘SUN’, 600,’renhe’)
insert into DailyIncome values (‘SPIKE’, ‘FRI’, 300,’renhe’)
insert into DailyIncome values (‘SPIKE’, ‘WED’, 500,’renhe’)
insert into DailyIncome values (‘SPIKE’, ‘FRI’, 300,’renhe’)
insert into DailyIncome values (‘JOHNS’, ‘THU’, 800,’renhe’)
insert into DailyIncome values (‘JOHNS’, ‘SAT’, 800,’renhe’)
insert into DailyIncome values (‘SPIKE’, ‘TUE’, 100,’renhe’)
insert into DailyIncome values (‘SPIKE’, ‘THU’, 300,’bolaiya’)
insert into DailyIncome values (‘FREDS’, ‘WED’, 500,’bolaiya’)
insert into DailyIncome values (‘SPIKE’, ‘SAT’, 100,’bolaiya’)
insert into DailyIncome values (‘FREDS’, ‘SAT’, 500,’bolaiya’)
insert into DailyIncome values (‘FREDS’, ‘THU’, 800,’bolaiya’)
insert into DailyIncome values (‘JOHNS’, ‘TUE’, 600,’bolaiya’)
3、方式一：使用select sum case when then end as 方式实现行转列
select VendorId ,
sum(case when IncomeDay=’MoN’ then IncomeAmount else 0 end) as MON,
sum(case when IncomeDay=’TUE’ then IncomeAmount else 0 end) as TUE,
sum(case when IncomeDay=’WED’ then IncomeAmount else 0 end) as WED,
sum(case when IncomeDay=’THU’ then IncomeAmount else 0 end) as THU,
sum(case when IncomeDay=’FRI’ then IncomeAmount else 0 end) as FRI,
sum(case when IncomeDay=’SAT’ then IncomeAmount else 0 end) as SAT,
sum(case when IncomeDay=’SUN’ then IncomeAmount else 0 end) as SUN
from DailyIncome group by VendorId
4、使用pivot函数实现行转列
select * from (select VendorId,IncomeAmount,IncomeDay from DailyIncome) as #temp1
pivot
(
sum (IncomeAmount) —-第三步
for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) —第二步
) as AvgIncomePerDay
第一步：肯定是要明白数据源了，这里是DailyIncome
第二步：要明白要想让哪一列的值做新的列名字
第三步：要明白对于这新的列要求那些值
注：使用pivot函数，会吧没选中的其他列作为group by的分组条件
5、使用变量
DECLARE @sql11 VARCHAR(max)

SET @sql11=” –初始化变量@sql

SELECT @sql11=@sql11+’,[‘+ IncomeDay +’]’ FROM DailyIncome

GROUP BY IncomeDay –变量多值赋值

SET @sql11=stuff(@sql11,1,1,”)–去掉首个’,’
–print @sql11

SET @sql11=’select VendorId,’+@sql11+’ from DailyIncome
pivot (max (IncomeAmount) for IncomeDay in (‘+@sql11++’)) as MaxIncomePerDay’
exec(@sql11)
来源：https://blog.csdn.net/qingwenc/article/details/108382885