declare @tb table([year] int,[quatar] int,[money] decimal(4,1))
insert @tb
select 1990,1,1.1 union
select 1990,2,1.2 union
select 1990,3,1.3 union
select 1990,4,1.4 union
select 1991,1,2.1 union
select 1991,2,2.2 union
select 1991,3,2.3 union
select 1991,4,2.4 select
[Q1]=sum(case when quatar=1 then [money] else 0 end),
[Q2]=sum(case when quatar=2 then [money] else 0 end),
[Q3]=sum(case when quatar=3 then [money] else 0 end),
[Q4]=sum(case when quatar=4 then [money] else 0 end)
from @tb A group by [year]--结果
/*Q1 Q2 Q3 Q4
----------------------------------------
1.1 1.2 1.3 1.4
2.1 2.2 2.3 2.4(所影响的行数为 2 行)
*/
insert @tb
select 1990,1,1.1 union
select 1990,2,1.2 union
select 1990,3,1.3 union
select 1990,4,1.4 union
select 1991,1,2.1 union
select 1991,2,2.2 union
select 1991,3,2.3 union
select 1991,4,2.4 select
[Q1]=sum(case when quatar=1 then [money] else 0 end),
[Q2]=sum(case when quatar=2 then [money] else 0 end),
[Q3]=sum(case when quatar=3 then [money] else 0 end),
[Q4]=sum(case when quatar=4 then [money] else 0 end)
from @tb A group by [year]--结果
/*Q1 Q2 Q3 Q4
----------------------------------------
1.1 1.2 1.3 1.4
2.1 2.2 2.3 2.4(所影响的行数为 2 行)
*/
insert @tb
select 1990,1,1.1 union
select 1990,2,1.2 union
select 1990,3,1.3 union
select 1990,4,1.4 union
select 1991,1,2.1 union
select 1991,2,2.2 union
select 1991,3,2.3 union
select 1991,4,2.4 select [year],
[Q1]=sum(case when quatar=1 then [money] else 0 end),
[Q2]=sum(case when quatar=2 then [money] else 0 end),
[Q3]=sum(case when quatar=3 then [money] else 0 end),
[Q4]=sum(case when quatar=4 then [money] else 0 end)
from @tb A group by [year]--结果
/*year Q1 Q2 Q3 Q4
------------------------------------------------
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4(所影响的行数为 2 行)
*/
RETURNS money
AS
BEGIN
DECLARE @je money
select @je = money from privot where year = @y and quatar = @quatar
return(@je)
endselect DISTINCT year,dbo.uf_rtnje(year,'1') '01',dbo.uf_rtnje(year,'2') '02',dbo.uf_rtnje(year,'3') '03',dbo.uf_rtnje(year,'4') '04' from privot