Page History: Multi-Row String Concatenation - SQL Server

Compare Page Revisions

1 0 Current 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Fri, Nov 15, 2013, 1:14 PM

Given a set of records having a text field, we would like to concatenate all the values in the text field into a single comma-separated string. For example, consider a table having three records with a text field containing the value of "Hewey" in the first record, "Dewey" in the second, and "Louie" in the third. We would like a method that would return "Hewey, Dewey, Louie".

Technique #1: Using a Scalar Function¶

The following technique uses a scalar function to concatenate all the field values together.

CREATE FUNCTION [dbo].[GenerateValidTimeCodes](@ResourceId int)
RETURNS VARCHAR(1000) AS

BEGIN
    DECLARE @sResult varchar(1000)

    SELECT 
        @sResult = COALESCE(@sResult + ', ', '') + TimeCodeQualifier
    FROM 
        TimeCodeSowExtended t
        INNER JOIN ResourceOdcSow r
            ON r.SowId = t.SowId
    WHERE 
        r.ResourceId = @ResourceId
    ORDER BY 
        TimeCodeQualifier

    RETURN coalesce(@sResult, '')
END

Using SELECT DISTINCT¶

with a as (
    select distinct 
        pe.Selector
    from 
        dbo.PageElement pe
        
        inner join dbo.PageElementRole per
            on pe.PageElementID = per.PageElementID
            
        inner join dbo.aspnet_UsersInRoles uir
            on uir.RoleId = per.RoleID
            
        inner join dbo.aspnet_Users u
            on u.UserId = uir.UserId
            and u.UserName = @username
            
    where 1=1
        and @url like pe.UrlPattern
    )
select
    @result = coalesce(@result + '|', '') + Selector
from
    a

Technique #2: Using FOR XML¶

The following technique uses the FOR XML clause in a SELECT statement to concatenate all the field values together.

create procedure [dbo].[GetProgramLevelClients] 
    (
    @CpProjectIds        xml
    ) as 
/*
declare
    @CpProjectIds xml
select
    @CpProjectIds = '<x v="6F2377F0-98C1-4F44-A0BC-31D1BD26EE22"/>'
        + '<x v="4BA689CB-D269-4F7F-848C-2A10544BFE6C"/>'
        + '<x v="6A87EA34-94D8-4189-AFEA-9A3BE2E309E7"/>'
        + '<x v="3459FDF5-B193-47DC-A6C0-8E894FF60EC3"/>'
;
*/

with MyProjects as (
    select distinct
         ce.WbClientName
        ,ce.CpCustomerId
        ,pe.CpProjectId
    from 
        QATesting.dbo.ProjectsExtended pe
        inner join QATesting.dbo.CustomersExtended ce
            on pe.CpCustomerId = ce.CpCustomerId
    where 1=1
        and @CpProjectIds.exist('/x[@v = sql:column("pe.CpProjectId")]') = 1

    )

select distinct
     ClientName      = WbClientName
    ,ClientId        = CpCustomerId
    ,CpProjectIds    = (select "x/@v" = convert(varchar(40), CpProjectId)
                        from MyProjects mp2
                        where mp.CpCustomerId = mp2.CpCustomerId
                        order by CpProjectId
                        for xml path ('')
                        )
from
    MyProjects mp

order by
     ClientName

Technique #3: Using a Recursive Common-Table-Expression¶

with fk as (
    select 
         ForeignKey     = 'FK_OrderDetail_OrderHeader'
        ,OnColumn       = '[OrderDetail].[AgencyId]'
    union select 
         ForeignKey     = 'FK_OrderDetail_OrderHeader'
        ,OnColumn       = '[OrderDetail].[OrderId]'
    union select 
         ForeignKey     = 'FK_OrderDetail_OrderHeader'
        ,OnColumn       = '[OrderDetail].[RegionId]'
    )
,MyData as (
    select
         ForeignKey
        ,OnColumn
        ,RowNumber      = row_number() over (partition by ForeignKey order by OnColumn)
        ,ColCount       = count(1) over (partition by ForeignKey)
    from
        fk
    )
,RecursiveData as (

    -- base case
    select
         RowNumber
        ,ForeignKey
        ,OnColumn
        ,ColCount
        ,OnColumnAggregate = convert(varchar(max), OnColumn)
        ,Lvl = 1
    from
        MyData a


    -- recursive case
    union all select
         a.RowNumber
        ,a.ForeignKey
        ,a.OnColumn
        ,a.ColCount
        ,OnColumnAggregate = convert(varchar(max), b.OnColumnAggregate + ', ' + a.OnColumn)
        ,Lvl = b.Lvl + 1
    from
        MyData a
        inner join RecursiveData b    
            on a.RowNumber = b.RowNumber+1

    )
select 
     ForeignKey
    ,OnColumnAggregate
from 
    RecursiveData 
where 1=1 
    and ColCount = Lvl