unitid applynum state
62361 1 1
62361 1 1
62361 1 2
62361 1 0
62361 1 3执行这个查询 为什么结果没有合并?
SELECT UnitiD,[0] 未审核,[1] 已通过,[2] 未通过
FROM FormationApply f
PIVOT(SUM(f.ApplyNum) FOR f.State IN ([0],[1],[2])) AS pvt
ORDER BY unitid结果是
unitid 未审核 已通过 未通过
62361 NULL 1 NULL
62361 NULL 1 NULL
62361 NULL NULL 1
62361 1 NULL NULL
62361 NULL NULL NULL
62361 1 1
62361 1 1
62361 1 2
62361 1 0
62361 1 3执行这个查询 为什么结果没有合并?
SELECT UnitiD,[0] 未审核,[1] 已通过,[2] 未通过
FROM FormationApply f
PIVOT(SUM(f.ApplyNum) FOR f.State IN ([0],[1],[2])) AS pvt
ORDER BY unitid结果是
unitid 未审核 已通过 未通过
62361 NULL 1 NULL
62361 NULL 1 NULL
62361 NULL NULL 1
62361 1 NULL NULL
62361 NULL NULL NULL
SUM(CASE WHEN state = 0 THEN applynum ELSE 0 END) AS 未审核,
SUM(CASE WHEN state = 1 THEN applynum ELSE 0 END) AS 已通过,
SUM(CASE WHEN state = 2 THEN applynum ELSE 0 END) AS 未通过
FROM @TEST
GROUP BY unitid
SELECT UnitiD,[0] 未审核,[1] 已通过,[2] 未通过
FROM @TEST f
PIVOT(SUM(f.ApplyNum) FOR f.State IN ([0],[1],[2])) AS pvt
ORDER BY unitid我这里是过是正确的,你可以试一下我的代码
if object_id('a1','u') is not null
drop table a1
create table a1
( BH varchar(10),SP varchar(20),GSBH varchar(10))
insert into a1 select '001','汽车','001' union all select '002','树','001' union all
select '003','汽车','002' union all select '004','自行车','002' union all
select '005','汽车','003' union all select '006','汽车','001'--表二
if object_id('a2','u') is not null
drop table a2
create table a2
(BH varchar(10),GS varchar(20))
insert into a2 select '001','公司1' union all select '002','公司2' union all
select '003','公司3'--表三
if object_id('a3','u') is not null
drop table a3
create table a3
(BH varchar(10),QY varchar(20),GSBH varchar(20))
insert into a3 select '001','区域1','001,002' union all select '002','区域2','001,003'--表四
if object_id('a4','u') is not null
drop table a4select a.BH,a.SP,a.GSBH,b.GS,c.QY
into a4 from a1 as a,a2 as b,a3 as c
where (a.GSBH = b.BH and b.BH = substring(c.GSBH,0,charindex(',',c.GSBH)))
or (a.GSBH = b.BH and b.BH = substring(c.GSBH,charindex(',',c.GSBH)+1,len(c.GSBH)-charindex(',',c.GSBH)))--select * from a4select *,p.树+p.汽车+p.自行车 as 总计
from
(
select QY,SP from a4) as p
pivot(count(SP) for SP in(汽车,树,自行车)) as p
[0] 未审核,
[1] 已通过,
[2] 未通过
from (select unitid,applynum,[state] from FormationApply) f
pivot(sum(ApplyNum) FOR [State] IN ([0],[1],[2])) AS pvt
order by unitid
还有个问题哈 大大 这个sum(ApplyNum) 我想改为count(*) 因为我是按记录条数来计算的。请问该怎么写一下呢
select UnitiD,
[0] 未审核,
[1] 已通过,
[2] 未通过
from (select unitid,applynum,[state] from FormationApply) f
pivot(count(ApplyNum) FOR [State] IN ([0],[1],[2])) AS pvt
order by unitid