Select
DIstinct
Cat2,
Cat3,
'Amount_A' As amount,
(Select Amount_A from #TmpA Where Order_month=2 And Cat2=A.Cat2 And Cat3=A.Cat3) As '2月',
(Select Amount_A from #TmpA Where Order_month=3 And Cat2=A.Cat2 And Cat3=A.Cat3) As '3月',
(Select Amount_A from #TmpA Where Order_month=4 And Cat2=A.Cat2 And Cat3=A.Cat3) As '4月',
(Select Amount_A from #TmpA Where Order_month=5 And Cat2=A.Cat2 And Cat3=A.Cat3) As '5月',
(Select Amount_A from #TmpA Where Order_month=6 And Cat2=A.Cat2 And Cat3=A.Cat3) As '6月',
(Select Amount_A from #TmpA Where Order_month=7 And Cat2=A.Cat2 And Cat3=A.Cat3) As '7月'
from #TmpA A
Union All
Select
DIstinct
Cat2,
Cat3,
'Amount_B' As amount,
(Select Amount_B from #TmpA Where Order_month=2 And Cat2=A.Cat2 And Cat3=A.Cat3) As '2月',
(Select Amount_B from #TmpA Where Order_month=3 And Cat2=A.Cat2 And Cat3=A.Cat3) As '3月',
(Select Amount_B from #TmpA Where Order_month=4 And Cat2=A.Cat2 And Cat3=A.Cat3) As '4月',
(Select Amount_B from #TmpA Where Order_month=5 And Cat2=A.Cat2 And Cat3=A.Cat3) As '5月',
(Select Amount_B from #TmpA Where Order_month=6 And Cat2=A.Cat2 And Cat3=A.Cat3) As '6月',
(Select Amount_B from #TmpA Where Order_month=7 And Cat2=A.Cat2 And Cat3=A.Cat3) As '7月'
from #TmpA A
Order By Cat2,Cat3 Desc
DIstinct
Cat2,
Cat3,
'Amount_A' As amount,
(Select Amount_A from #TmpA Where Order_month=2 And Cat2=A.Cat2 And Cat3=A.Cat3) As '2月',
(Select Amount_A from #TmpA Where Order_month=3 And Cat2=A.Cat2 And Cat3=A.Cat3) As '3月',
(Select Amount_A from #TmpA Where Order_month=4 And Cat2=A.Cat2 And Cat3=A.Cat3) As '4月',
(Select Amount_A from #TmpA Where Order_month=5 And Cat2=A.Cat2 And Cat3=A.Cat3) As '5月',
(Select Amount_A from #TmpA Where Order_month=6 And Cat2=A.Cat2 And Cat3=A.Cat3) As '6月',
(Select Amount_A from #TmpA Where Order_month=7 And Cat2=A.Cat2 And Cat3=A.Cat3) As '7月'
from #TmpA A
Union All
Select
DIstinct
Cat2,
Cat3,
'Amount_B' As amount,
(Select Amount_B from #TmpA Where Order_month=2 And Cat2=A.Cat2 And Cat3=A.Cat3) As '2月',
(Select Amount_B from #TmpA Where Order_month=3 And Cat2=A.Cat2 And Cat3=A.Cat3) As '3月',
(Select Amount_B from #TmpA Where Order_month=4 And Cat2=A.Cat2 And Cat3=A.Cat3) As '4月',
(Select Amount_B from #TmpA Where Order_month=5 And Cat2=A.Cat2 And Cat3=A.Cat3) As '5月',
(Select Amount_B from #TmpA Where Order_month=6 And Cat2=A.Cat2 And Cat3=A.Cat3) As '6月',
(Select Amount_B from #TmpA Where Order_month=7 And Cat2=A.Cat2 And Cat3=A.Cat3) As '7月'
from #TmpA A
Order By Cat2,Cat3 Desc
create table #TmpA
(
Cat2 varchar(32),
Cat3 varchar(32),
Order_year int,
Order_month int,
Amount_A decimal(18,2),
Amount_B decimal(18,2)
)
--插入数据
Insert #TmpA Values('K', 'S', 2005, 2, 10, 100)
Insert #TmpA Values('K', 'S', 2005, 3, 20, 200)
Insert #TmpA Values('K', 'S', 2005, 4, 30, 300)
Insert #TmpA Values('K', 'S', 2005, 5, 40, 400)
Insert #TmpA Values('K', 'S', 2005, 6, 50, 500)
Insert #TmpA Values('K', 'S', 2005, 7, 60, 600)
Insert #TmpA Values('K', 'B', 2005, 2, 10, 100)
Insert #TmpA Values('K', 'B', 2005, 3, 20, 200)
Insert #TmpA Values('K', 'B', 2005, 4, 30, 300)
Insert #TmpA Values('K', 'B', 2005, 5, 40, 400)
Insert #TmpA Values('K', 'B', 2005, 6, 50, 500)
Insert #TmpA Values('K', 'B', 2005, 7, 60, 600)
--测试
Select
DIstinct
Cat2,
Cat3,
'Amount_A' As amount,
(Select Amount_A from #TmpA Where Order_month=2 And Cat2=A.Cat2 And Cat3=A.Cat3) As '2月',
(Select Amount_A from #TmpA Where Order_month=3 And Cat2=A.Cat2 And Cat3=A.Cat3) As '3月',
(Select Amount_A from #TmpA Where Order_month=4 And Cat2=A.Cat2 And Cat3=A.Cat3) As '4月',
(Select Amount_A from #TmpA Where Order_month=5 And Cat2=A.Cat2 And Cat3=A.Cat3) As '5月',
(Select Amount_A from #TmpA Where Order_month=6 And Cat2=A.Cat2 And Cat3=A.Cat3) As '6月',
(Select Amount_A from #TmpA Where Order_month=7 And Cat2=A.Cat2 And Cat3=A.Cat3) As '7月'
from #TmpA A
Union All
Select
DIstinct
Cat2,
Cat3,
'Amount_B' As amount,
(Select Amount_B from #TmpA Where Order_month=2 And Cat2=A.Cat2 And Cat3=A.Cat3) As '2月',
(Select Amount_B from #TmpA Where Order_month=3 And Cat2=A.Cat2 And Cat3=A.Cat3) As '3月',
(Select Amount_B from #TmpA Where Order_month=4 And Cat2=A.Cat2 And Cat3=A.Cat3) As '4月',
(Select Amount_B from #TmpA Where Order_month=5 And Cat2=A.Cat2 And Cat3=A.Cat3) As '5月',
(Select Amount_B from #TmpA Where Order_month=6 And Cat2=A.Cat2 And Cat3=A.Cat3) As '6月',
(Select Amount_B from #TmpA Where Order_month=7 And Cat2=A.Cat2 And Cat3=A.Cat3) As '7月'
from #TmpA A
Order By Cat2,Cat3 Desc
--删除测试环境
Drop table #TmpA
--结果
/*
Cat2 Cat3 amount 2月 3月 4月 5月 6月 7月
K S Amount_A 10.00 20.00 30.00 40.00 50.00 60.00
K S Amount_B 100.00 200.00 300.00 400.00 500.00 600.00
K B Amount_A 10.00 20.00 30.00 40.00 50.00 60.00
K B Amount_B 100.00 200.00 300.00 400.00 500.00 600.00
*/
set @s_A = ''
set @s_B = ''select
@s_A = @s_A + ',['+rtrim(order_month)+'月]=max(case when order_month = '+rtrim(order_month)+' then amount_A end)',
@s_B = @s_B + ',['+rtrim(order_month)+'月]=max(case when order_month = '+rtrim(order_month)+' then amount_B end)'
from
#TmpA group by order_month order by order_monthset @s_A = 'select cat2,cat3,amount=''amount_A'''+@s_A+' from #TmpA group by cat2,cat3' +
' UNION ALL ' +
'select cat2,cat3,amount=''amount_B'''+@s_B+' from #TmpA group by cat2,cat3'+
' order by cat2,cat3 desc,amount'print @s_Aexec(@s_A)
Declare @Sql1 Nvarchar(4000)
Declare @Sql2 Nvarchar(4000)
Set @Sql1='Select DIstinct Cat2,Cat3,''Amount_A'' As amount,'
Set @Sql2='Select DIstinct Cat2,Cat3,''Amount_B'' As amount,'
Select @Sql1=@Sql1+'(Select Amount_A from #TmpA Where Order_month='+Rtrim(Order_month)+' And Cat2=A.Cat2 And Cat3=A.Cat3) As '''+Rtrim(Order_month)+N'月'','
from (Select Distinct Order_month from #TmpA ) A Order By Order_month
Select @Sql2=@Sql2+'(Select Amount_B from #TmpA Where Order_month='+Rtrim(Order_month)+' And Cat2=A.Cat2 And Cat3=A.Cat3) As '''+Rtrim(Order_month)+N'月'','
from (Select Distinct Order_month from #TmpA ) A Order By Order_month
Select @Sql1=Left(@Sql1,Len(@Sql1)-1)+'from #TmpA A Union All '+Left(@Sql2,Len(@Sql2)-1)+'from #TmpA A Order By Cat2,Cat3 Desc'
EXEC(@Sql1)