请教个问题 急急急
如图,想实现如下效果
medcode medname wmedposition
3Y00090077 银丹脑通软胶囊 421,422,423,424,425
1T00080061 头孢克洛咀嚼片 581,597,613,629,645
[最优解释]
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
--> 测试数据:#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
----------------结果----------------------------
/*
medcodemedname(无列名)
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
[其他解释]