有這樣兩個表：
table_a:
id name val
1 aaa 30
2 aaa 40
3 aaa 50
1 bbb 35
2 bbb 45
3 bbb 55
table_b:
name val1 val2 val3
aaa 30 40 50
bbb 35 45 55
請用一個SQL語句實現table_a到table_b的轉化。
1
create table tb(id int, name varchar(10), val int)
2insert into tb values(1 , 'aaa' , 30 )
3insert into tb values(2 , 'aaa' , 40 )
4insert into tb values(3 , 'aaa' , 50 )
5insert into tb values(1 , 'bbb' , 35 )
6insert into tb values(2 , 'bbb' , 45 )
7insert into tb values(3 , 'bbb' , 55 )
8go
9
10--sql 2000靜態，指ID只有1,2,3
11select name ,
12 sum(case id when 1 then val else 0 end) [val1],
13 sum(case id when 2 then val else 0 end) [val2],
14 sum(case id when 3 then val else 0 end) [val3]
15from tb
16group by name
17
/**//*
18
name val1 val2 val3
19---------- ----------- ----------- -----------
20aaa 30 40 50
21bbb 35 45 55
22
23（所影響的行數為 2 行）
24
*/
25
26--sql 2000動態，指ID不確定
27declare @sql varchar(8000)
28set @sql = 'select name '
29select @sql = @sql + ' , sum(case id when ''' + cast(id as varchar) + ''' then val else 0 end) [val' + cast(id as varchar) + ']'
30from (select distinct id from tb) as a
31set @sql = @sql + ' from tb group by name'
32exec(@sql)
33/**//*
34name val1 val2 val3
35---------- ----------- ----------- -----------
36aaa 30 40 50
37bbb 35 45 55
38*/
39
40
41drop table tb
42
posted on 2009-08-17 10:14
absolute 閱讀(443)
評論(0) 編輯 收藏 所屬分類:
DataBase