Concatenating Rows

  • carl.anderson-1037280

    Mr or Mrs. 500

    Points: 522

    Comments posted to this topic are about the item Concatenating Rows

  • nick.mcdermaid

    Hall of Fame

    Points: 3779

    Perhaps something in your requirements precluded this method but how about this:

    DECLARE @var varchar(8000)

    SET @var = ''

    SELECT @var = @var + FieldName + '/'

    FROM Table

    SELECT @Var

  • nicolay.tchernitsky

    SSC Rookie

    Points: 31

    the XML solution can also be:

    select replace(

    convert(

    varchar(max),

    (select name+',' as [text()] from fruit order by id for xml path(''))

    )+',,,'

    ,',,,,'

    ,'')

    or, if there is no need in deleting the last comma and converting the result from xml to string, just

    select name+',' as [text()] from fruit order by id for xml path('')

  • Calvin Lawson

    SSChampion

    Points: 11030

    To Modify:

    DECLARE @var varchar(8000)

    SELECT @var = ISNULL(@var + '/') + FieldName

    FROM Table

    SELECT @Var

    However, this only works for one entity. For muliple entities the CTE recursion works fine, although it performs like crap. I only use this for throwaway SQL code generators where string handling in SQL makes sense.

    For a scalable solution where it makes sense to do this in SQL, you need to turn the recursion into iteration. Most CTE recursion can be re-written to be iterative, allowing a set based solution.

    Signature is NULL

  • Jeff Moden

    SSC Guru

    Points: 994266

    Nicely written article but even the optimized solution causes a full cartesian product on the far right of the execution plan. That could explain it's performance problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994266

    nicolay.tchernitsky (10/13/2009)


    the XML solution can also be:

    select replace(

    convert(

    varchar(max),

    (select name+',' as [text()] from fruit order by id for xml path(''))

    )+',,,'

    ,',,,,'

    ,'')

    or, if there is no need in deleting the last comma and converting the result from xml to string, just

    select name+',' as [text()] from fruit order by id for xml path('')

    Flip it around and delete the first comma...

    with

    fruit as (

    select 'Apple' as name, 101 as id UNION ALL

    select 'Banana' as name, 102 as id UNION ALL

    select 'Orange' as name, 103 as id UNION ALL

    select 'Melon' as name, 104 as id UNION ALL

    select 'Grape' as name, 105 as id

    )

    SELECT STUFF((select ','+name from fruit order by id for xml path('')),1,1,'') AS CSV

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jon Spink

    SSCarpal Tunnel

    Points: 4010

    this is mostly useful when you have to group rows together by one field and concatenate the corresponding values for each group. How would the solution look then?

  • dystarry

    SSC Rookie

    Points: 34

    SELECT STUFF((

    SELECT ', ' + name

    FROM dbo.FRUIT

    FOR XML PATH('')

    ), 1, 2, '');

  • a.everett

    SSC Rookie

    Points: 32

    so as newbie in the top post quotes why not just use a select @var+ to concatenate.

    You said it thats fine for 1 entity granted can see that.

    But just move the code to a function that accepts the entity pk key and returns the concat string.

    Then just call the function from a select thats listing the entities you want reporting?

    somthing like,

    select entkey, getmylist(entkey) from myentlist

  • Jon Spink

    SSCarpal Tunnel

    Points: 4010

    neat one dystarry. And here's the solution for rows grouped by type_id:

    declare @type_id int

    declare cursor1 CURSOR FAST_FORWARD FOR SELECT distinct type_id FROM fruit

    drop table #Temp

    create table #Temp (region_id int, atext varchar(100))

    open cursor1

    fetch next from cursor1 into @type_id

    while @@fetch_status = 0

    begin

    print @type_id

    insert into #Temp (region_id, atext)

    select distinct @type_id,

    STUFF((SELECT ', ' + name FROM dbo.fruit where type_id = @type_id FOR XML PATH('')), 1, 2, '')

    from fruit

    --group by region_id

    fetch next from cursor1 into @type_id

    end

    close cursor1

    deallocate cursor1

    select * from #Temp

  • Rob Fisk

    SSCommitted

    Points: 1841

    Grouping by other values, possibly from another table may look like this:

    /**

    * Test Data

    **/

    DECLARE @ParentItem TABLE(ParentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ParentName NVARCHAR(50), ParentDate DATETIME);

    DECLARE @ChildItem TABLE(ChildID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ParentID INT, ChildName NVARCHAR(50));

    INSERT @ParentItem(ParentName,ParentDate)

    SELECT 'The first one','01 jan 2009' UNION ALL

    SELECT 'The second one','01 feb 2009'

    INSERT @ChildItem(ParentID,ChildName)

    SELECT 1,'Cat' UNION ALL

    SELECT 1,'Dog' UNION ALL

    SELECT 1,'Monkey' UNION ALL

    SELECT 2,'Teapot' UNION ALL

    SELECT 2,'Merkin' UNION ALL

    SELECT 2,'Grommet'

    /**

    * The concatination

    **/

    SELECT p.ParentID, p.ParentName,

    STUFF((SELECT ', ' + ChildName FROM @ChildItem WHERE ParentID = p.ParentID FOR XML PATH('')),1,2,'')

    FROM @ParentItem p

    GROUP BY p.ParentID, p.ParentName

    [font="Arial"]_______________________________________________________
    Change is inevitable... Except from a vending machine.[/font]

  • Jon Spink

    SSCarpal Tunnel

    Points: 4010

    that's much better!

  • Joel Mansford

    SSC Enthusiast

    Points: 122

    In my opinion the 'neatest' solution is to use a custom CLR aggregate function. Afterall this is an aggregate function. Happily MS provide a concatenate as an example or custom aggregates (first example):

    http://technet.microsoft.com/en-us/library/ms131056.aspx

    I appreciate that for many DBAs rolling out CLR code is a bit of a culture change but it only needs to be done once and the performance seems to be fine.

  • Koenraad Dendievel

    SSC Veteran

    Points: 289

    You're missing several other solutions

    3. using UDF, coalesce and a subview

    ALTER FUNCTION dbo.Concatenate_Functiebenaming (@ID_Personeelslid INT)

    returns varchar(200)

    as BEGIN

    declare @STR varchar(202)

    select @STR = coalesce(@str, '') + ', ' + Ambt + ISNULL(': '+Functie, '')

    from [vw_Personeelsleden_Huidige_Benaming_Sub]

    where [ID_Personeelslid] = @ID_Personeelslid

    return substring(@str, 3, 200)

    END

    you call it like this

    CREATE VIEW dbo.vw_Personeelsleden_Huidige_Benaming

    SELECT DISTINCT ID_Personeelslid, dbo.Concatenate_Functiebenaming(ID_Personeelslid) AS Functiebenaming

    FROM dbo.vw_Personeelsleden_Huidige_Benaming_Sub

    GROUP BY ID_Personeelslid

    4. UDF aggregate function with CLR (google it)

    and

    hey, here is an overview of almost all (solution 3 above is not there) + comment on drawbacks

    http://www.projectdmx.com/tsql/rowconcatenate.aspx

  • Rob Fisk

    SSCommitted

    Points: 1841

    Joel Mansford (10/14/2009)


    In my opinion the 'neatest' solution is to use a custom CLR aggregate function. Afterall this is an aggregate function. Happily MS provide a concatenate as an example or custom aggregates (first example):

    http://technet.microsoft.com/en-us/library/ms131056.aspx

    I appreciate that for many DBAs rolling out CLR code is a bit of a culture change but it only needs to be done once and the performance seems to be fine.

    The other big problem with this is if you have no idea how to actually compile the DLL or do not have the requisite software to do so.

    I had a look at the example, and even with the other one showing 2 parameters I was at a complete loss as to how to alter the aggregation to take a parameter for custom separator.

    [font="Arial"]_______________________________________________________
    Change is inevitable... Except from a vending machine.[/font]

Viewing 15 posts - 1 through 15 (of 160 total)

You must be logged in to reply to this topic. Login to reply