在VB里好实现。
不过用存储过程没试过。
1.select distinct 项次 from (select 項次,站別,sum(數量) as 数量
from ...
group by 項次,站別 ) group by 項次
2.select distinct 站別 from (select 項次,站別,sum(數量) as 数量
from ...
group by 項次,站別 ) group by 站別
3.select 項次,站別,sum(數量) as 数量
from ...
group by 項次,站別
然后再判断项次,站别,依次写出来就可以了。
Select * into tmpABC from (Select 1 as 项次,N'A1' as 站别, 3 as 数量
union select 1,N'B1',2
union select 2,N'B1',5
union select 3,N'C1',6
union select 4,N'D1',4
union select 5,N'A1',7) a
select * from tmpABC
declare @StrCol2 nvarchar(1000)
declare @StrSql nvarchar(1000)
declare @Col2 nvarchar(10)
set @StrCol2=''
set @StrSql=''
declare cursor_out cursor local for Select distinct 站别 from tmpABC
open cursor_out
fetch next from cursor_out into @Col2
while @@fetch_status=0
begin
set @StrCol2=@StrCol2 + ' , (case when 站别= ''' + @Col2 + ''' then 数量 else null end) as ' + @Col2
fetch next from cursor_out into @Col2
end
CLOSE cursor_out
DEALLOCATE cursor_out
set @StrSql='Select 项次' + @StrCol2 + ' from tmpABC'
select 項次,
A1=(case 站別 when A1 then 數量 else null end),
B1=(case 站別 when B1 then 數量 else null end),
C1=(case 站別 when C1 then 數量 else null end),
D1=(case 站別 when D1 then 數量 else null end)
FROM 表T1