查询结果合并用逗号分隔

ERP导入基础资料需要将货品尺码横排并用逗号分隔

方式一

DECLARE @WZ1 VARCHAR
SET @WZ1='1'
SELECT DISTINCT STUFF((SELECT ',' + GGDM + QUOTENAME(ggmc) FROM guige2 WHERE GGWZ1=@WZ1 FOR XML PATH('')),1,1,'')
FROM   GUIGE2 AS g
WHERE g.GGWZ1=@WZ1

方式二

DECLARE @str VARCHAR(MAX)
SET @str=''
SELECT @str += ',' + GGDM + QUOTENAME(GGMC) FROM GUIGE2 WHERE GGWZ1='1'
SELECT STUFF(@str,1,1,'')

方式三

declare @str varchar(MAX)
select @str = case isnull(@str, '') when '' then '' else @str + ',' end +GGDM + QUOTENAME(GGMC) FROM GUIGE2 WHERE GGWZ1='1'
select @str

 案例

SELECT vsm.SPDM AS 商品代码,
       vsm.SPMC AS 商品名称,
       (
           SELECT STUFF((SELECT  DISTINCT ',' + GG1DM + QUOTENAME(GG1MC) FROM VW_ShangpinMX WHERE SPDM=vsm.SPDM FOR XML PATH('')),1,1,'') 
       ) AS 颜色明细,
       (
           SELECT STUFF((SELECT DISTINCT ',' + GG2DM + QUOTENAME(GG2MC) FROM VW_ShangpinMX  WHERE SPDM=vsm.SPDM FOR XML PATH('')),1,1,'')
       ) AS 尺码明细
FROM   VW_ShangpinMX AS vsm
WHERE  vsm.SPDM LIKE '23%'  --减少数据量做了查询限制
GROUP BY vsm.SPDM,vsm.SPMC
ORDER BY vsm.SPDM

转载于:https://www.cnblogs.com/win32pro/p/8073326.html