34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT t.cPersonCode 检查人, t.cUnusualType 检查类别 , MAX(合格) AS 合格 , MAX(不合格) AS 不合格
FROM ( SELECT a.cPersonCode , b.cUnusualType , SUM(CASE WHEN b.bQualified = 1 THEN 1 ELSE 0 END) AS 合格 ,
SUM(CASE WHEN b.bQualified = 2 THEN 1 ELSE 0 END) AS 不合格 FROM dbo.t_HrJobInspect a
INNER JOIN dbo.t_HrJobInspectEntry b ON b.cCode = a.cCode WHERE a.cCheckDate = '2017-09-01'
GROUP BY cCheckDate , a.cPersonCode , b.cUnusualType , bQualified ) t
GROUP BY t.cPersonCode , t.cUnusualType
ORDER BY t.cPersonCode,t.cUnusualType
CREATE TABLE #tmp1
(
checkman VARCHAR(20), checktype VARCHAR(20), pass INT, nopass INT
)
INSERT INTO #tmp1
SELECT 'a001', '5S类', 25, 5 UNION ALL
SELECT 'a001', '质量类', 29, 1 UNION ALL
SELECT 'b002', '计划类', 20, 5
DECLARE @str VARCHAR(MAX),@sql VARCHAR(MAX)
SET @str=N''
SET @sql=N''
SELECT @str=@str+N',['+checktype+N'(pass)]'+N',['+checktype+N'(nopass)]'
FROM #tmp1
GROUP BY checktype
SELECT @str=STUFF(@str,1,1,N'')
SET @sql=N'
SELECT checkman,'+@str+'
FROM (
SELECT checkman, checktype+''(''+checktype_p+'')'' AS checktype_p,val
FROM (
SELECT checkman, checktype, SUM(pass) AS pass, SUM(nopass) AS nopass
FROM #tmp1
GROUP BY checkman, checktype
) a
UNPIVOT(val FOR checktype_p IN (pass, nopass)) b
) c
PIVOT(MAX(val) FOR checktype_p IN ('+@str+')) p'
EXEC (@sql)
checkman 5S类(pass) 5S类(nopass) 计划类(pass) 计划类(nopass) 质量类(pass) 质量类(nopass)
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
a001 25 5 NULL NULL 29 1
b002 NULL NULL 20 5 NULL NULL