将某列的值，多行拼接成一行

--需求：需要查询每张核销中的存在的货号，各货号用逗号分隔

--1、使用自定义函数
CREATE  FUNCTION GetCheckGoodsNo ( @CheckID VARCHAR(10) )
RETURNS NVARCHAR(MAX)
AS 
BEGIN
DECLARE @RESULT NVARCHAR(MAX)
SELECT  @RESULT = ISNULL(@RESULT + ',', '') + RTRIM(Goods_No)
FROM    CheckGoods
WHERE   CheckID = @CheckID
RETURN @RESULT
END
GO

SELECT  CheckID, dbo.GetCheckGoodsNo(CheckID) AS GoodsNo
FROM    [Check]

/*
CheckID        GoodsNo
---------   -----------
KGZ0000006    A01,A01
KGZ0000007    G01
KGZ0000008    A01,A01
KGZ0000009    A01,A02,A03
*/

--2、SQL2005以上环境使用XML查询。


SELECT  CheckID, Goods_No
FROM    CheckGoods 
/*
CheckID    Goods_No
---------- ---------------
KGZ0000001   A01            
KGZ0000002   A01            
KGZ0000002   A02            
KGZ0000002   A03   
*/
SELECT  a.CheckID, STUFF(( SELECT   ',' + RTRIM(Goods_No)
FROM     CheckGoods
WHERE    CheckID = a.CheckID 
FOR
                           XML PATH('')
                         ), 1, 1, '') AS GoodsNo
FROM    [Check] a
/*
CheckID    GoodsNo
---------- -----------------
KGZ0000001   A01
KGZ0000002   A01,A02,A03
*/

 

转载于:https://www.cnblogs.com/ToddLai/archive/2011/12/14/2287372.html