select fsid,
max(case flag when 0 then portcode else '' end) pol,
max(case flag when 1 then portcode else '' end) pod
from tb
group by fsid
max(case flag when 0 then portcode else '' end) pol,
max(case flag when 1 then portcode else '' end) pod
from tb
group by fsid
INSERT @T SELECT 8 , 468, 0
INSERT @T SELECT 9 , 468, 0
INSERT @T SELECT 9, 453, 1
SELECT FSID,
SUM(CASE WHEN flag =0 THEN portcode ELSE 0 END ),
SUM(CASE WHEN flag =1 THEN portcode ELSE 0 END )
FROM @T GROUP BY FSID
/*
FSID
----------- ----------- -----------
8 468 0
9 468 453(影響 2 個資料列)
*/
insert into tb values(8 , '468' , 0 )
insert into tb values(9 , '468' , 0 )
insert into tb values(9 , '453' , 1 )
goselect fsid,
max(case flag when 0 then cast(portcode as varchar) else '' end) pol,
max(case flag when 1 then cast(portcode as varchar) else '' end) pod
from tb
group by fsiddrop table tb/*
fsid pol pod
----------- ------------------------------ ------------------------------
8 468
9 468 453(所影响的行数为 2 行)*/
INSERT @T SELECT 8 , 468, 0
INSERT @T SELECT 9 , 468, 0
INSERT @T SELECT 9, 453, 1
SELECT FSID,
SUM(CASE WHEN flag =0 THEN portcode ELSE 0 END )POL,
SUM(CASE WHEN flag =1 THEN portcode ELSE 0 END )POD
FROM @T GROUP BY FSID
/*FSID POL POD
----------- ----------- -----------
8 468 0
9 468 453(影響 2 個資料列)
*/
SELECT FSID,
Pol = SUM(CASE WHEN flag =0 THEN portcode ELSE 0 END ),
POD = SUM(CASE WHEN flag =1 THEN portcode ELSE 0 END )
FROM TB GROUP BY FSID
declare @tb table (fdid int,portcode int,flag int)
insert into @tb select 8,468,0
union all select 9,468,0
union all select 9,453,1
select fdid,pol=sum(case when flag=0 then portcode else '' end),
pod=sum(case when flag=1 then portcode else 0 end)
from @tb group by fdid
fdid pol pod
----------- ----------- -----------
8 468 0
9 468 453(2 行受影响)
但是用sum,难以理解,它是用来合计的阿!?
create table tb(FSID int, portcode varchar(10), flag int)
insert into tb values(8 , '468' , 0 )
insert into tb values(9 , '468' , 0 )
insert into tb values(9 , '453' , 1 )
goselect fsid,
max(case flag when 0 then cast(portcode as varchar) else '0' end) pol,
max(case flag when 1 then cast(portcode as varchar) else '0' end) pod
from tb
group by fsiddrop table tb/*
fsid pol pod
----------- ------------------------------ ------------------------------
8 468 0
9 468 453(2 個資料列受到影響)
*/
select * from (select * from #T) a pivot(max(portcode) for flag in (0,1)) b这个为什么就错了呢
提示:
消息 102,级别 15,状态 1,第 1 行
'0' 附近有语法错误。
if object_id('tb') is not null drop table tbcreate table tb(FSID int, portcode varchar(10), flag int)
insert into tb values(8 , '468' , 0 )
insert into tb values(9 , '468' , 0 )
insert into tb values(9 , '453' , 1 )
goselect fsid,[0] as pol,[1] as [pod] from tb
pivot
(max(portcode) for flag in([0],[1])) bfsid pol pod
----------- ---------- ----------
8 468 NULL
9 468 453(2 行受影响)
max(case flag when 0 then portcode else null end) pol,
max(case flag when 1 then portcode else null end) pod
from tb
group by fsid