将每个Row列值连接到SQL中以逗号分隔的字符串

I'm using the AdventureWorks2008R2 sample database.

我正在使用AdventureWorks2008R2示例数据库。

i need an output like each row should be concatenated into string(column values will be separated by commas)

我需要一个输出,如每行应连接成字符串(列值将用逗号分隔)

This is my code:

这是我的代码:

    create proc sales5
    as begin 
    declare @SalesPersonID int 

    create table #table 
    (
        id int identity(1,1) primary key clustered,
        SalesPersonID int
    )

    create table #table1
    ( 
        SalesPersonID int null , 
        PostalCode nvarchar (15) null,City varchar (30) null 
    ) 

    insert into #table 
       select distinct  
          SA.BusinessEntityID 
       from 
          Sales.SalesPerson SA 
       join 
          Sales.SalesOrderHeader S on S.SalesPersonID = SA.BusinessEntityID   
                                   and S.SalesPersonID is not null

   ;WITH CTE
AS (
    SELECT DISTINCT s.SalesPersonID
        , CAST(A.PostalCode AS NVARCHAR(25)) AS PostalCode
        , A.City
        , CAST(s.SalesPersonID AS VARCHAR(MAX)) AS SortOrder    
    FROM Sales.SalesOrderHeader S(NOLOCK)
        , Person.Person P(NOLOCK)
        , Person.Address A(NOLOCK)
        , #table t
    WHERE s.SalesPersonID IS NOT NULL
        AND S.SalesPersonID = P.BusinessEntityID
        AND S.SalesPersonID = A.AddressID
        AND S.SalesPersonID IN ('274')

    UNION ALL

    SELECT a.SalesOrderID
        , a.SalesOrderNumber
        , NULL
        , C.SortOrder + CAST(a.SalesOrderID AS VARCHAR(MAX))   
    FROM CTE C
    INNER JOIN Sales.SalesOrderHeader A(NOLOCK)
        ON A.SalesPersonID = C.SalesPersonID
    )
SELECT *
FROM cte
union all
 select 
         cast ( P.BusinessEntityID as nvarchar(50)),
         cast (P.NationalIDNumber as nvarchar(30)),
         cast (P.Gender as  nvarchar(20)),
         cast (P.BusinessEntityID as  VARCHAR(MAX)) + cast ('1' as  VARCHAR(MAX))
    from   HumanResources.Employee P(nolock)
    where exists(
    select 1 from CTE C
        where P.BusinessEntityID=C.SalesPersonID  )
ORDER BY SortOrder

    set nocount off  
end 

My output is like :

我的输出如下:

SalesPersonID    PostalCode   City    SortOrder
274                98027    Issaquah    274
274              502097814    M         2741
43849              SO43849   NULL     27443849
44082              SO44082   NULL     27444082
44508              SO44508   NULL     27444508
44532              SO44532   NULL     27444532

My Expected Output:

我的预期成果:

274,98027,Issaquah   
274,502097814,M        
43849,SO43849
44082,SO44082   
44508,SO44508    
44532,SO44532   

Note:no need of sortorder Column ,thats y i necglected in output.

注意:不需要sortorder Column,这就是我在输出中所记录的。

Any one please help on this.. Thanks in advance.

任何人请帮助... ..提前致谢。

1 个解决方案

#1

You can concatenate your result using + or CONCAT like this.

您可以使用+或CONCAT来连接结果。

SELECT LTRIM(SalesPersonID) + ',' + LTRIM(PostalCode) + ISNULL(',' +  LTRIM(City),'') 
FROM CTE2 
ORDER BY SortOrder

Complete Query

   ;WITH CTE
AS (
    SELECT DISTINCT s.SalesPersonID
        , CAST(A.PostalCode AS NVARCHAR(25)) AS PostalCode
        , A.City
        , CAST(s.SalesPersonID AS VARCHAR(MAX)) AS SortOrder    
    FROM Sales.SalesOrderHeader S(NOLOCK)
        , Person.Person P(NOLOCK)
        , Person.Address A(NOLOCK)
        , #table t
    WHERE s.SalesPersonID IS NOT NULL
        AND S.SalesPersonID = P.BusinessEntityID
        AND S.SalesPersonID = A.AddressID
        AND S.SalesPersonID IN ('274')

    UNION ALL

    SELECT a.SalesOrderID
        , a.SalesOrderNumber
        , NULL
        , C.SortOrder + CAST(a.SalesOrderID AS VARCHAR(MAX))   
    FROM CTE C
    INNER JOIN Sales.SalesOrderHeader A(NOLOCK)
        ON A.SalesPersonID = C.SalesPersonID
    ), CTE2 as 
    (
SELECT *
FROM cte
union all
 select 
         cast ( P.BusinessEntityID as nvarchar(50)),
         cast (P.NationalIDNumber as nvarchar(30)),
         cast (P.Gender as  nvarchar(20)),
         cast (P.BusinessEntityID as  VARCHAR(MAX)) + cast ('1' as  VARCHAR(MAX))
    from   HumanResources.Employee P(nolock)
    where exists(
    select 1 from CTE C
        where P.BusinessEntityID=C.SalesPersonID  )
)
SELECT LTRIM(SalesPersonID) + ',' + LTRIM(PostalCode) + ISNULL(',' +  LTRIM(City),'') 
FROM CTE2 
ORDER BY SortOrder