22,209
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([medcode] VARCHAR(10),[medname] VARCHAR(14),[wmedposition] INT)
INSERT #tb
SELECT '3Y00090077','银丹脑通软胶囊',421 UNION ALL
SELECT '3Y00090077','银丹脑通软胶囊',422 UNION ALL
SELECT '3Y00090077','银丹脑通软胶囊',423 UNION ALL
SELECT '3Y00090077','银丹脑通软胶囊',424 UNION ALL
SELECT '3Y00090077','银丹脑通软胶囊',425 UNION ALL
SELECT '1T00080061','头孢克洛咀嚼片',581 UNION ALL
SELECT '1T00080061','头孢克洛咀嚼片',597 UNION ALL
SELECT '1T00080061','头孢克洛咀嚼片',613 UNION ALL
SELECT '1T00080061','头孢克洛咀嚼片',629 UNION ALL
SELECT '1T00080061','头孢克洛咀嚼片',645
--------------开始查询--------------------------
SELECT medcode,medname,
STUFF((SELECT ','+LTRIM(wmedposition) FROM #tb WHERE medcode=t.medcode AND medname=t.medname FOR XML PATH('')),1,1,'')
FROM #tb AS t
GROUP BY medcode,medname
----------------结果----------------------------
/*
medcode medname (无列名)
1T00080061 头孢克洛咀嚼片 581,597,613,629,645
3Y00090077 银丹脑通软胶囊 421,422,423,424,425
*/
SELECT medcode,medname,
STUFF((SELECT ','+LTRIM(wmedposition) FROM TB WHERE medcode=t.medcode AND medname=t.medname),1,1,'')
FROM TB AS t
GROUP BY medcode,medname
select a.medcode ,a.medname
stuff((select ','+wmedposition from tb b
where b.medcode=a.medcode and b.medname=a.medname
for xml path('')),1,1,'') 'wmedposition'
from tb a
group by a.medcode ,a.medname