1.--如果type固定
Select
SUM(Case type When 'A' Then amount Else 0 End) As A,
SUM(Case type When 'B' Then amount Else 0 End) As B,
SUM(Case type When 'C' Then amount Else 0 End) As C
From
table1--如果type不固定
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ', SUM(Case type When ''' + type + ''' Then amount Else 0 End) As ' + type
From table1 Group By type
Select @S = ' Select ' + Stuff(@S, 1, 1, '') + ' From table1'
EXEC(@S)
Select
SUM(Case type When 'A' Then amount Else 0 End) As A,
SUM(Case type When 'B' Then amount Else 0 End) As B,
SUM(Case type When 'C' Then amount Else 0 End) As C
From
table1--如果type不固定
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ', SUM(Case type When ''' + type + ''' Then amount Else 0 End) As ' + type
From table1 Group By type
Select @S = ' Select ' + Stuff(@S, 1, 1, '') + ' From table1'
EXEC(@S)
--结果1
--如果tyear固定
Select
,
SUM(Case tyear When 2006 Then amount Else 0 End) As [2006],
SUM(Case tyear When 2007 Then amount Else 0 End) As [2007]
From
table2
Group By
--如果tyear不固定
Declare @S Varchar(8000)
Select @S = 'Select '
Select @S = @S + ', SUM(Case tyear When ' + Rtrim(tyear) + ' Then amount Else 0 End) As [' + Rtrim(tyear) + ']'
From table2 Group By tyear
Select @S = @S + ' From table2 Group By '
EXEC(@S)--结果2
Select
,
SUM(amount) As amount
From
table2
Group By