sql 针对多个id或名称的分割和组合

SELECT nPushID
    INTO   #temp
    FROM  Table1
    WHERE  CHARINDEX( ‘,‘ + CONVERT(VARCHAR(10), nCoulmn) + ‘,‘, ‘,‘ + ‘3305,3311,3314‘ + ‘,‘) > 0
SELECT * FROM #temp

--实例1 分割符在前
select isnull ((select ‘@‘+ CommodityName from OrderInfo where OrderInfo.orderid =OrderMain.OrderID for xml path(‘‘)),‘‘) from OrderMain
where  orderid =‘20120302121548‘

--针对分隔符在前，可以用stuff很方便
select isnull (stuff((select ‘@‘+ CommodityName from OrderInfo where OrderInfo.orderid =OrderMain.OrderID for xml path(‘‘)),1,1,‘‘),‘‘) from OrderMain
where  orderid =‘20120302121548‘

--实例2 同表，分隔符在后
SELECT  distinct vcName,dtCreateTime,
                    ISNULL ((select CONVERT(VARCHAR(5),nCode)+‘,‘ from Table1 a where a.vcName =b.vcName AND a.dtCreateTime=b.dtCreateTime for xml path(‘‘)),‘‘)AS vcCode
                    FROM Table1 b

--分隔符在后，对于拼接名称的，个人觉得用substring、left还麻烦点，不如在后台或者前台去处理