日期:2014-05-17 浏览次数:20362 次
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,
STUFF((SELECT ','+LTRIM(wmedposition) FROM TB WHERE medcode=t.medcode AND medname=t.medname),1,1,'')
FROM TB AS t
GROUP BY medcode,medname