SQl 語句

[yifan1008]

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

[paoluo]

--如果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)

[Linux_9]

select
A,
1 = case when C = 1 then B else 0 end,
2 = case when C = 2 then B else 0 end
from Table

[Linux_9]

呵呵，我的有问题。看来高手就是高手。
这个我收藏了。
--如果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)

[fwacky]

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

[Linux_9]

呵呵，高手都来回答这样的问题得分，我问的问题都没有人光顾。

[ice241018]

学习

[ojuju10]

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)

[xiaqilin]

值得学习学习

[paoluo]

...