# 如何實現數據的交叉交換

dongfang2003 2004-08-23 09:34:13

1 A1 3
1 B1 2
2 B1 5
3 C1 6
4 D1 4
5 A1 7

1 3 2
2 5
3 6
4 4
5 7

...全文
88 点赞 收藏 10

10 条回复

qgbin 2004-08-30

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 項次,站別

lihonggen0 2004-08-27

http://dev.csdn.net/develop/article/18/18622.shtm

sangshuyezi 2004-08-27

sangshuyezi 2004-08-27

dongfang2003 2004-08-27

sangshuyezi 2004-08-26
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'

exec(@StrSql)

drop table tmpABC

dongfang2003 2004-08-26

dongfang2003 2004-08-24

dongfang2003 2004-08-24

Andy__Huang 2004-08-23
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

1188

VB 数据库(包含打印，安装，报表)

2004-08-23 09:34