SQl 語句
A B C 字段
S1 30 1
S1 20 2
S2 30 1
S3 50 2
要得到查詢的結果是
A 1 2 字段
S1 30 20
S2 30 0
S3 0 50
[解决办法]
select
A,
[1]=sum(case C when 1 then B else 0 end),
[2]=sum(case C when 2 then B else 0 end)
from
表
group by
A
[解决办法]
--如果C是固定的
Select
A,
SUM(Case C When 1 Then B Else 0 End) As [1],
SUM(Case C When 2 Then B Else 0 End) As [2]
From
TableName
Group By
A
--如果C是不固定的
Declare @S Varchar(8000)
Select @S = 'Select A '
Select @S = @S + ' , SUM(Case C When ' + Cast(C As Varchar) + ' Then B Else 0 End) As [ ' + Cast(C As Varchar) + '] '
From TableName Group By C
Select @S = @S + ' From TableName Group By A '
EXEC(@S)
[解决办法]
select
A,
1 = case when C = 1 then B else 0 end,
2 = case when C = 2 then B else 0 end
from Table
[解决办法]
呵呵，我的有问题。看来高手就是高手。
这个我收藏了。
--如果C是不固定的
Declare @S Varchar(8000)
Select @S = 'Select A '
Select @S = @S + ' , SUM(Case C When ' + Cast(C As Varchar) + ' Then B Else 0 End) As [ ' + Cast(C As Varchar) + '] '
From TableName Group By C
Select @S = @S + ' From TableName Group By A '
EXEC(@S)
[解决办法]
create table yifan1008
(
A varchar(10),
B varchar(10),
C varchar(10)
)
insert into yifan1008
select
'S1 ' , '30 ' , '1 ' union all select
'S1 ' , '20 ', '2 ' union all select
'S2 ' , '30 ' , '1 ' union all select
'S3 ' , '50 ' , '2 '
select A ,
max( case C when '1 ' then B else '0 ' end) as '1 ',
max( case C when '2 ' then B else '0 ' end) as '2 '
from yifan1008 group by A
A 1 2 字段
S1 30 20
S2 30 0
S3 0 50
[解决办法]
create table tbl
(
A varchar(10),
B varchar(10),
C varchar(10)
)
insert into tbl
select
'S1 ' , '30 ' , '1 ' union all select
'S1 ' , '20 ', '2 ' union all select
'S2 ' , '30 ' , '1 ' union all select
'S3 ' , '50 ' , '2 '
--静态的
select a ,
max(case when c= '1 ' then b else 0 end) as [1],
max(case when c= '2 ' then b else 0 end) as [2]
from tbl group by a
--动态的
declare @i varchar(1000)
set @i= 'select a '
select @i=@i+ ',max(case when c= ' ' '+RTRIM(c)+ ' ' ' then b else 0 end) as '+quotename(c)
from (select distinct c from tbl ) aa
set @i=@i+ ' from tbl group by a '
exec(@i)
[解决办法]
值得学习学习
