请问这张表该如何写SQL？

[anuoxiang]

原表如下：
F1 F2 F3
------------
A S01 3
A S02 3
A S03 3
B S01 4
B S02 4
B S03 4
C S01 5
C S02 5
C S03 5
C S04 5

请问如何写SQL使其变成如下表？
F2 A B C SUM
-------------------------
S01 3 4 5 12
S02 3 4 5 12
S03 3 4 5 12
S04 0 0 5 5


A，B，C这个字段是已知的。

[xpace]

子查询
select
from (
select ... from
select ... from
...
)

[anuoxiang]

能写的详细一些吗？看不懂哦。。。。

[anuoxiang]

顶一下。

[shi11]

可以這樣寫：
SELECT F1,A,B,C,A+B+C AS "SUM" FROM (
SELECT C.F1,C.F2,NVL(A.F3,0) AS A,NVL(B.F3,0) AS B,C.F3 AS C
FROM
(SELECT F1,F2,F3 FROM SH_TEST WHERE F1='A') A,
(SELECT F1,F2,F3 FROM SH_TEST WHERE F1='B') B,
(SELECT F1,F2,F3 FROM SH_TEST WHERE F1='C') C
WHERE A.F2=B.F2(+)
AND A.F2(+)=C.F2
)

[bluesky1980]

学习

[cb8011]

顶

[shawnzhao]

select F2,sum(A),sum(B),sum(C),sum(D)
from
(
select F2,decode(F1,'A',F3,0) A
,decode(F1,'B',F3,0) B
,decode(F1,'C',F3,0) C
,(decode(F1,'A',F3,0)+decode(F1,'B',F3,0)+decode(F1,'C',F3,0)) D
from table_name
)
group by F2

[wangrong001]

select F2,decode(sum(A),'','0',sum(A),sum(A)) A,decode(sum(B),'','0',sum(B),sum(B)) B,decode(sum(C),'','0',sum(C),sum(C)) C,decode(sum(SUM),'','0',sum(SUM),sum(SUM)) SUM
from
(
select f2,decode(f1,'a',f3) A,decode(f1,'b',0) B,decode(f1,'c',0) C,decode(f1,'a',f3) SUM
from rong
where f1='a' union
select f2,decode(f1,'a',0) A,decode(f1,'b',f3) B,decode(f1,'c',0) C,decode(f1,'b',f3) SUM
from rong
where f1='b' union
select f2,decode(f1,'a',0) A,decode(f1,'b',0) B,decode(f1,'c',f3) C,decode(f1,'c',f3) SUM
from tabelName
where f1='c'
)
group by f2
兄弟，这准没错，我验证了

[wangrong001]

select F2,decode(sum(A),'','0',sum(A),sum(A)) A,decode(sum(B),'','0',sum(B),sum(B)) B,decode(sum(C),'','0',sum(C),sum(C)) C,decode(sum(SUM),'','0',sum(SUM),sum(SUM)) SUM
from
(
select f2,decode(f1,'a',f3) A,decode(f1,'b',0) B,decode(f1,'c',0) C,decode(f1,'a',f3) SUM
from tableName
where f1='a' union
select f2,decode(f1,'a',0) A,decode(f1,'b',f3) B,decode(f1,'c',0) C,decode(f1,'b',f3) SUM
from tableName
where f1='b' union
select f2,decode(f1,'a',0) A,decode(f1,'b',0) B,decode(f1,'c',f3) C,decode(f1,'c',f3) SUM
from tableName
where f1='c'
)
group by f2

上头的有两个地方rong改为tabelName了，试试！

[xuzhengchang1029]

create table #a (department nvarchar(50),goodsname nvarchar(50),quantity int)

insert into #a values ('部门M','货品A',100)

insert into #a values ('部门M','货品B',200)

insert into #a values ('部门N','货品C',200)

insert into #a values ('部门N','货品B',5)

declare @sql varchar(8000)

set @sql = 'select department ,'

select @sql = @sql + 'sum(case goodsname when '''+goodsname+'''

then quantity else 0 end) as '+goodsname+','

from (select distinct goodsname from #a ) as a

select @sql = left(@sql,len(@sql)-1) + ' from #a group by department'



exec(@sql)

drop table #a

go
你看看这段,就可以实现

[NoThatNo]

全是笨蛋，看我的：
select F2, sum(A),Sum(B),Sum(C),Sum(和)
from
(
select A=F3,B=0,C=0,和=F3 from tableName where F1='A'
select 0,F3,0,F3 from tableName where F1='B'
select 0,0,F3,F3 from tableName where F1='C'
) a
group by F2

[johnnyshabi]

喂上面的!最基本的语法都不懂,还回复!

[火电]

原表如下：
F1 F2 F3
------------
A S01 3
A S02 3
A S03 3
B S01 4
B S02 4
B S03 4
C S01 5
C S02 5
C S03 5
C S04 5

请问如何写SQL使其变成如下表？
F2 A B C SUM
-------------------------
S01 3 4 5 12
S02 3 4 5 12
S03 3 4 5 12
S04 0 0 5 5

[火电]

select f2,
case when f1 = A then f3 else 0 end as A,
case when f1 = B then f3 else 0 end as B,
case when f1 = C then f3 else 0 end as C,
SUM(F3) AS SUM
FROM TABLE
GROUP BY F2

[windf726]

都是写什么东西啊
没一个正确的

[soapyboy]

-------------------------------
--***************************--
-------------------------------
create table test(f1 char(1),f2 char(4),f3 int)

insert into test(F1,F2,F3)
SELECT 'A','S01','3'
UNION ALL SELECT 'A', 'S02','3'
UNION ALL SELECT 'A', 'S03','3'
UNION ALL SELECT 'B', 'S01','4'
UNION ALL SELECT 'B', 'S02','4'
UNION ALL SELECT 'B', 'S03','4'
UNION ALL SELECT 'C', 'S01','5'
UNION ALL SELECT 'C', 'S02','5'
UNION ALL SELECT 'C', 'S03','5'
UNION ALL SELECT 'C', 'S04','5'
-------------------------------
--***************************--
-------------------------------

SELECT F2,
SUM(CASE f1 WHEN 'A' THEN F3 ELSE 0 END) AS A,
SUM(CASE f1 WHEN 'B' THEN F3 ELSE 0 END) AS B,
SUM(CASE f1 WHEN 'C' THEN F3 ELSE 0 END) AS C,
SUM(F3) AS SUM
FROM TEST
GROUP BY F2
-------------------------------
--***************************--
-------------------------------

F2 A B C SUM
-------------------------
S01 3 4 5 12
S02 3 4 5 12
S03 3 4 5 12
S04 0 0 5 5

[soapyboy]

--或


SELECT P1.*, (P1.A + P1.B + P1.C ) AS SUM
FROM (SELECT F2,
SUM(CASE P.F1 WHEN 'A' THEN P.F3 ELSE 0 END) AS A,
SUM(CASE P.F1 WHEN 'B' THEN P.F3 ELSE 0 END) AS B,
SUM(CASE P.F1 WHEN 'C' THEN P.F3 ELSE 0 END) AS C

FROM TEST AS P
GROUP BY P.F2) AS P1
GO