ALTER proc [dbo].
[PIVOT_Test]
@year1 varchar(
4),
@year2 varchar(
4),
@year3 varchar(
4),
@quarter varchar(
2)
as
select column1,
sum(
column2) column2,
year,
month into #t1
from table1
where year in(
@year1,
@year2,
@year3)
and 1=case when isnull(
@quarter,
0)
=0 then 1
when @quarter=1 and month in(
1,
2,
3)
then 1
when @quarter=2 and month in(
4,
5,
6)
then 1
when @quarter=3 and month in(
7,
8,
9)
then 1
when @quarter=4 and month in (
10,
11,
12)
then 1 end
group by column1,
year,
month
select column2 ,a.
year,b.productcode,b.ProductValueCN
into #t2
from #t1 a
left join table2 b
on a.packagecode
=b.packagecode
left join table3 c
on a.packagecode
=c.packagecode
and c.
Year=a.
Year and c.
Month=a.
Month
where ProductCode
is not null
group by a.
year,b.productcode,b.ProductValueCN
--动态列转行
declare @sql varchar(
max)
set @sql='select ProductCode+''(''+ProductValueCN+'')'' as ProductValueCN,isnull(['+@year1+']/1000,0) as year1,isnull(['+@year2+']/1000,0) as year2,isnull(['+@year3+']/1000,0) as year3 from #t2
as p PIVOT ( SUM(salescount) FOR year IN ('+'['+@year1+'],['+@year2+'],['+@year3+']'+') ) AS T order by ProductValueCN'
print @sql
exec (
@sql)
drop table #t2
drop table #t1
转载于:https://www.cnblogs.com/daniel-niu/p/10688574.html
相关资源：SQL行列转换 Pivot UnPivot
