如图，想实现如下效果
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
----------------结果----------------------------
/*
medcode medname (无列名)
1T00080061 头孢克洛咀嚼片 581,597,613,629,645
3Y00090077 银丹脑通软胶囊 421,422,423,424,425
*/
------其他解决方案--------------------
select medcode,medname
wmedposition=stuff((select ','+wmedposition from tb
where medcode=a.medcode and medname=a.medname for xml path('')),1,1,'')
from tb group by medcode,medname
------其他解决方案--------------------
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
------其他解决方案--------------------
搜下 行转列
------其他解决方案--------------------
代码高亮怎么这么丑呢？