这样复杂的主从明细表查询如何实现？

[hiyun]

主表：tabl1
ID Date col1 col2
1 2007-1-1 111 222
2 2007-2-1 234 245
3 2007-3-1 456 367
从表：tabl2
ID Name Date value
1 aaa 2007-1-1 345
2 bbb 2007-1-1 245
3 ccc 2007-1-1 223
4 ddd 2007-1-1 355
1 aaa 2007-2-1 423
2 bbb 2007-2-1 345
3 ccc 2007-2-1 568
4 ddd 2007-2-1 435
1 aaa 2007-3-1 356
2 bbb 2007-3-1 123
3 ccc 2007-3-1 536
4 ddd 2007-3-1 341
现在要得到这样的查询结果：
Date col1 col2 aaa bbb ccc ddd
2007-1-1 111 222 345 245 223 355
2007-2-1 234 245 423 345 568 435
2007-3-1 456 367 356 123 536 341
也就是说将同一天的从表的name列的值转换为主表的列名，value列转换为对应的值。

[paoluo]

--如果name是固定的

Select
A.[Date],
A.col1,
A.col2,
SUM(Case B.Name When 'aaa' Then value Else 0 End) As aaa,
SUM(Case B.Name When 'bbb' Then value Else 0 End) As bbb,
SUM(Case B.Name When 'ccc' Then value Else 0 End) As ccc,
SUM(Case B.Name When 'ddd' Then value Else 0 End) As ddd
From
tabl1 A
Inner Join
tabl2 B
On A.[Date] = B.[Date]
Group By
A.[Date],
A.col1,
A.col2

[paoluo]

--如果name不是固定的
Declare @S Varchar(8000)
Select @S = ' Select A.[Date], A.col1, A.col2'
Select @S = @S + ', SUM(Case B.Name When ''' + Name + ''' Then value Else 0 End) As [' + Name + ']'
From tabl2 Group By Name Order By Min(ID)
Select @S = @S + ' From tabl1 A Inner Join tabl2 B On A.[Date] = B.[Date] Group By A.[Date], A.col1, A.col2'
EXEC(@S)

[paoluo]

--創建測試環境
Create Table tabl1
(ID Int,
[Date] Varchar(10),
col1 Int,
col2 Int)
Insert tabl1 Select 1, '2007-1-1', 111, 222
Union All Select 2, '2007-2-1', 234, 245
Union All Select 3, '2007-3-1', 456, 367

Create Table tabl2
(ID Int,
Name Varchar(10),
[Date] Varchar(10),
value Int)
Insert tabl2 Select 1, 'aaa', '2007-1-1', 345
Union All Select 2, 'bbb', '2007-1-1', 245
Union All Select 3, 'ccc', '2007-1-1', 223
Union All Select 4, 'ddd', '2007-1-1', 355
Union All Select 1, 'aaa', '2007-2-1', 423
Union All Select 2, 'bbb', '2007-2-1', 345
Union All Select 3, 'ccc', '2007-2-1', 568
Union All Select 4, 'ddd', '2007-2-1', 435
Union All Select 1, 'aaa', '2007-3-1', 356
Union All Select 2, 'bbb', '2007-3-1', 123
Union All Select 3, 'ccc', '2007-3-1', 536
Union All Select 4, 'ddd', '2007-3-1', 341
GO
--測試
--如果name是固定的
Select
A.[Date],
A.col1,
A.col2,
SUM(Case B.Name When 'aaa' Then value Else 0 End) As aaa,
SUM(Case B.Name When 'bbb' Then value Else 0 End) As bbb,
SUM(Case B.Name When 'ccc' Then value Else 0 End) As ccc,
SUM(Case B.Name When 'ddd' Then value Else 0 End) As ddd
From
tabl1 A
Inner Join
tabl2 B
On A.[Date] = B.[Date]
Group By
A.[Date],
A.col1,
A.col2

--如果name不是固定的
Declare @S Varchar(8000)
Select @S = ' Select A.[Date], A.col1, A.col2'
Select @S = @S + ', SUM(Case B.Name When ''' + Name + ''' Then value Else 0 End) As [' + Name + ']'
From tabl2 Group By Name Order By Min(ID)
Select @S = @S + ' From tabl1 A Inner Join tabl2 B On A.[Date] = B.[Date] Group By A.[Date], A.col1, A.col2'
EXEC(@S)
GO
--刪除測試環境
Drop Table tabl1, tabl2
--結果
/*
Date col1 col2 aaa bbb ccc ddd
2007-1-1 111 222 345 245 223 355
2007-2-1 234 245 423 345 568 435
2007-3-1 456 367 356 123 536 341
*/

[fa_ge]

我也來接點分，呵呵

我更多時候喜歡用靜態寫法，因為靜態寫法格式漂亮，不會象動態寫法那樣看起來有些亂的
感覺，這個想法可好笑.

[paoluo]

fa_ge(鶴嘯九天) ( ) 信誉：100 2007-08-06 16:07:11 得分: 0


我也來接點分，呵呵

我更多時候喜歡用靜態寫法，因為靜態寫法格式漂亮，不會象動態寫法那樣看起來有些亂的
感覺，這個想法可好笑.


-----------
那要看什麼情況了，如果name不固定，就沒辦法使用靜態的寫法了。