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)