22,210
社区成员
发帖
与我相关
我的任务
分享
/*--参考
CREATE TABLE #A (SH001 INT,SH002 VARCHAR(20),SH003 VARCHAR(80),SH004 VARCHAR(20))
INSERT INTO #A
SELECT 1,'C-20090013','采购课','414-200905' UNION ALL
SELECT 2,'C-20090012','采购课','047-200504' UNION ALL
SELECT 3,'C-20090016','工程一部','427-200303' UNION ALL
SELECT 5,'C-20090015','工程一部','427-200303' UNION ALL
SELECT 7,'C-20090013','采购课','222-200301' UNION ALL
SELECT 9,'C-20090014','采购课','222-200301' UNION ALL
SELECT 5,'C-20090015','工程二部','981-200801'
/*
SH001 SH002 采购课 工程一部 工程二部
2 C-20090012 047-200504
1 C-20090013 414-200905
7 C-20090013 222-200301
9 C-20090014 222-200301
5 C-20090015 427-200303 981-200801
3 C-20090016 427-200303
*/
;with CTE as (
Select SH001,SH002,ISNULL(采购课,'') 采购课,ISNULL(工程一部,'') 工程一部,ISNULL(工程二部,'') 工程二部
from (Select SH001,SH002,SH003,SH004 from #A ) a pivot (max(SH004) for SH003
in (采购课,工程一部,工程二部)) b
)
SELECT MIN(SH001) SH001,SH002
,[采购课]=STUFF((SELECT ','+采购课 FROM CTE WHERE SH002=c.SH002 FOR XML PATH('')), 1, 1, '')
,MAX(工程一部) [工程一部]
,MAX(工程二部) [工程二部]
FROM CTE c
GROUP BY SH002
/*
SH001 SH002 采购课 工程一部 工程二部
2 C-20090012 047-200504
1 C-20090013 414-200905,222-200301
9 C-20090014 222-200301
5 C-20090015 427-200303 981-200801
3 C-20090016 427-200303
*/
*/
IF OBJECT_ID('LI') IS NOT NULL
DROP TABLE LI
CREATE TABLE LI (SH001 INT ,SH002 VARCHAR(20),SH003 VARCHAR(20),SH004 VARCHAR(20))
INSERT INTO LI (SH001,SH002,SH003,SH004)
SELECT 1, 'C-20090013', '采购课', '414-200905' UNION ALL
SELECT 2, 'C-20090012', '采购课', '047-200504' UNION ALL
SELECT 3, 'C-20090016', '工程一部 ', '427-200303' UNION ALL
SELECT 5, 'C-20090015', '工程一部', '427-200303' UNION ALL
SELECT 1, 'C-20090013', '采购课', '222-200301' UNION ALL
SELECT 9, 'C-20090014', '采购课', '222-200301' UNION ALL
SELECT 5, 'C-20090015', '工程二部', '981-200801'
--查询结果
SELECT SH001,SH002,
MAX(CASE SH003 WHEN '采购课' THEN ISNULL(SH004,'') ELSE '' END) AS 采购课,
MAX(CASE SH003 WHEN '工程一部' THEN ISNULL(SH004,'') ELSE '' END) AS 工程一部,
MAX(CASE SH003 WHEN '工程二部' THEN ISNULL(SH004,'') ELSE '' END) AS 工程一部
FROM LI
GROUP BY SH001,SH002
ORDER BY SH001,SH002
/* 结果
SH001 SH002 采购课 工程一部 工程二部
2 C-20090012 047-200504
1 C-20090013 414-200905
9 C-20090014 222-200301
5 C-20090015 427-200303 981-200801
3 C-20090016 427-200303
*/