sql中pivot函数的使用

1.数据的创建

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

--VendorId 供应商ID,
--IncomeDay 收入时间
--IncomeAmount 收入金额

insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('SPIKE', 'MON', 300)
insert into DailyIncome values ('FREDS', 'SUN', 400)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'TUE', 200)
insert into DailyIncome values ('JOHNS', 'WED', 900)
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('JOHNS', 'MON', 300)
insert into DailyIncome values ('SPIKE', 'SUN', 400)
insert into DailyIncome values ('JOHNS', 'FRI', 300)
insert into DailyIncome values ('FREDS', 'TUE', 500)
insert into DailyIncome values ('FREDS', 'TUE', 200)
insert into DailyIncome values ('SPIKE', 'MON', 900)
insert into DailyIncome values ('FREDS', 'FRI', 900)
insert into DailyIncome values ('FREDS', 'MON', 500)
insert into DailyIncome values ('JOHNS', 'SUN', 600)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('JOHNS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'SAT', 800)
insert into DailyIncome values ('SPIKE', 'TUE', 100)
insert into DailyIncome values ('SPIKE', 'THU', 300)
insert into DailyIncome values ('FREDS', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'SAT', 100)
insert into DailyIncome values ('FREDS', 'SAT', 500)
insert into DailyIncome values ('FREDS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'TUE', 600)

平常的所用的是这样的

select VendorId ,
sum(case when  IncomeDay='MoN' then IncomeAmount else 0 end) MON,
sum(case when  IncomeDay='TUE' then IncomeAmount else 0 end) TUE,
sum(case when  IncomeDay='WED' then IncomeAmount else 0 end) WED,
sum(case when  IncomeDay='THU' then IncomeAmount else 0 end) THU,
sum(case when  IncomeDay='FRI' then IncomeAmount else 0 end) FRI,
sum(case when  IncomeDay='SAT' then IncomeAmount else 0 end) SAT,
sum(case when  IncomeDay='SUN' then IncomeAmount else 0 end) SUN
from DailyIncome group by VendorId

方式一:

select * from DailyIncome ----第一步
pivot 
(
sum (IncomeAmount) ----第三步
for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) ---第二步
) as AvgIncomePerDay

来解释下,要想用好Pivot函数,应该理解代码注释中的这几步。
第一步:肯定是要明白数据源了,这里是DailyIncome
第二步:要明白要想让哪一列的值做新的列名字
第三步:要明白对于这新的列要求那些值呢?

select * from DailyIncome
pivot (max (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay
where VendorId in ('SPIKE')

select top 10 * from DailyIncome

方式二:使用变量

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)
  • 1
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值