Concatenating Rows

  • d viz

    Ten Centuries

    Points: 1092

    how about this:

    declare @FinalString nvarchar(250)

    SET @FinalString=''

    SELECT @FinalString = COALESCE(@FinalString + ', ', '') + (CAST([name] AS nvarchar(50)))

    FROM Fruit

    order by [name]

    SELECT REPLACE(@FinalString,'''',' ')

  • Joel Mansford

    SSC Enthusiast

    Points: 122

    It's worth learning!

    With regard to software I could be wrong but I believe that Visual Studio Express[/url] is all that you need. The CLR stuff comes in useful in other areas for instance with Regular Expressions or for making external web service calls.

    One hacky way to support changing the separator character is to use something obscure in the code such as '¦' and then use the T-SQL REPLACE function to substitute it as required.

    Personally I just have a version for comma (',') and then another that uses semi-colon (';') as that keeps the usage of the function simple.

  • pgoebel

    Valued Member

    Points: 53

    and what about PIVOT?

  • dhechle

    SSC Eights!

    Points: 862

    I was trying this the same way as Koenraad Dendievel using a function i found elsewhere, seems to work a treat with this:

    CREATE FUNCTION dbo.ConcatFruit()

    RETURNS VARCHAR(8000)

    AS

    BEGIN

    DECLARE @Output VARCHAR(8000)

    SELECT @Output = COALESCE(@Output+', ', '') + Name

    FROM fruit

    RETURN @Output

    END

    GO

    SELECT TOP 1 dbo.ConcatFruit()

    FROM fruit

    GO

    DROP FUNCTION dbo.ConcatFruit

    GO

  • Carlo Romagnano

    SSC-Insane

    Points: 21965

    declare @l varchar(max)

    ,@comma varchar(2)

    set @l = ''

    set @comma = ''

    select @l = @l + @comma + name

    ,@comma = ', '

    from

    fruit

    ORDER BY name

    This is the more performant!

  • Tom Winter

    SSC Veteran

    Points: 296

    I've been using Solution 2, FOR XML PATH, for a while with good results. I use STUFF as SSChampion mentions to get rid of the extra separator.

    One other thing to watch for with FOR XML PATH is that since it's creating XML, it will encode the characters "<", ">", and "&". You'll need to unencode them manually. Something like this.

    -- The REPLACE calls undo the XML encoding

    -- and STUFF deletes the first comma and space

    REPLACE(REPLACE(REPLACE(STUFF(

    (

    SELECT

    ', ' + ...

    FROM

    ...

    FOR XML PATH('')

    )

    , 1, 2, ''), '& lt;', '<'), '& gt;', '>'), '& amp;', '&') -- See note below

    On the last line in the example, take out the spaces between the ampersands and the "lt;", "gt;" and "amp;". This forum software was encoding my example of unencoding so I couldn't get it display correctly. How ironic! 😉

  • benkoskysa

    SSC Enthusiast

    Points: 149

    Very helpful, thanks. You can also take out the dangling delimiter...

    DECLARE @var varchar(8000)

    SET @var = ''

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

    FROM Table

    SET @Var = substring(@Var,1,len(@Var)-1) -- Removes trailing slash

    SELECT @Var

  • Adam Haines

    SSC-Insane

    Points: 23197

    Tom Winter (10/14/2009)


    I've been using Solution 2, FOR XML PATH, for a while with good results. I use STUFF as SSChampion mentions to get rid of the extra separator.

    One other thing to watch for with FOR XML PATH is that since it's creating XML, it will encode the characters "<", ">", and "&". You'll need to unencode them manually. Something like this.

    -- The REPLACE calls undo the XML encoding

    -- and STUFF deletes the first comma and space

    REPLACE(REPLACE(REPLACE(STUFF(

    (

    SELECT

    ', ' + ...

    FROM

    ...

    FOR XML PATH('')

    )

    , 1, 2, ''), '& lt;', '<'), '& gt;', '>'), '& amp;', '&') -- See note below

    On the last line in the example, take out the spaces between the ampersands and the "lt;", "gt;" and "amp;". This forum software was encoding my example of unencoding so I couldn't get it display correctly. How ironic! 😉

    Actually you dont have replace the invalid characters if you use the FOR XML Clause, with the TYPE directive. You can then use an xquery to extract the value as it is precieved in the column, without replacing invalid chars.

    DECLARE @t TABLE(

    id INT,

    col CHAR(2)

    );

    INSERT INTO @t VALUES (1,'aa');

    INSERT INTO @t VALUES (1,'bb');

    INSERT INTO @t VALUES (1,'c>');

    INSERT INTO @t VALUES (2,'a<');

    SELECT

    id,

    STUFF(

    (

    SELECT ',' + col

    FROM @t t2

    WHERE t1.id = t2.id

    FOR XML PATH(''),TYPE

    ).value('.','VARCHAR(100)')

    ,1,1,'')

    FROM @t t1

    GROUP BY id

  • joe.strollo

    Grasshopper

    Points: 23

    I use the method below for concatenating multiple values into one. Wouldn't this work?

    -- Fill a temporary table variable with the data in the example

    DECLARE @TempTable TABLE

    ( idx int IDENTITY(1,1) PRIMARY KEY,

    fruit varchar(15) )

    INSERT INTO @TempTable (fruit) VALUES ('Apple')

    INSERT INTO @TempTable (fruit) VALUES ('Banana')

    INSERT INTO @TempTable (fruit) VALUES ('Grape')

    INSERT INTO @TempTable (fruit) VALUES ('Melon')

    INSERT INTO @TempTable (fruit) VALUES ('Orange')

    -- These three lines take the data from above and concatenate it into one field and then return the field value

    DECLARE @List varchar(Max)

    SELECT @List = COALESCE(@List + ', ','') + fruit FROM @TempTable ORDER BY fruit

    SELECT @List

  • Adam Haines

    SSC-Insane

    Points: 23197

    joe.strollo (10/14/2009)


    I use the method below for concatenating multiple values into one. Wouldn't this work?

    -- Fill a temporary table variable with the data in the example

    DECLARE @TempTable TABLE

    ( idx int IDENTITY(1,1) PRIMARY KEY,

    fruit varchar(15) )

    INSERT INTO @TempTable (fruit) VALUES ('Apple')

    INSERT INTO @TempTable (fruit) VALUES ('Banana')

    INSERT INTO @TempTable (fruit) VALUES ('Grape')

    INSERT INTO @TempTable (fruit) VALUES ('Melon')

    INSERT INTO @TempTable (fruit) VALUES ('Orange')

    -- These three lines take the data from above and concatenate it into one field and then return the field value

    DECLARE @List varchar(Max)

    SELECT @List = COALESCE(@List + ', ','') + fruit FROM @TempTable ORDER BY fruit

    SELECT @List

    This method only works if you want to return a single row. If you need to concatenate values, for each grouping, another method will have to be used, like FOR XML, UDF etc.

  • Jerry Day

    Old Hand

    Points: 374

    ------ CREATE AND LOAD THE TABLE

    create table aaatrash(id int, name varchar(20))

    insert into aaatrash

    select 1, 'apple'

    union all

    select 2, 'peach'

    union all

    select 3, 'grape'

    union all

    select 4, 'banana'

    union all

    select 5, 'melon'

    union all

    select 6, 'orange'

    ---------------------------------

    ---------- SOLUTION -----------

    declare @fruit varchar (200)

    select @fruit = coalesce(@fruit + ', ' + name, name ) from aaatrash

    select @fruit

    ---------------------------------

    Jerry D

  • Adam Seniuk

    SSCrazy

    Points: 2281

    I've been using Solution 2, "FOR XML PATH" for a while now. I use substring to remove the first separator, I think I will look into using stuff.

    I have learned something new today. I think I can kick back and enjoy the day now. 😀


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Mike Dougherty

    Ten Centuries

    Points: 1112

    also a typical response to this question is "don't ask the DB to format strings"

    ... meaning the consumer of this content should be responsible for display/output.

    I'm not sure there is a more 'performant' solution than to offload the presentation work to another tier better suited to the task.

  • john-999050

    Grasshopper

    Points: 15

    Why isn't the Coalesce function being used?:

    -- Results created neatly in one shot, more fields and other variations can be added 😎

    Select @SomeVariable = Coalesce(@SomeVariable + ',', '[if null]') + IsNull(SomeField, '')

    FROM tblSomeTable

    -- Remove last delimiter

    If Right(@SomeVariable , 1) = ',' Set @SomeVariable = Left(@SomeVariable , Len(@SomeVariable ) - 1)

  • sauraviit-689328

    Old Hand

    Points: 346

    the simplest one ....

    --create sample data set

    declare

    @table table (name varchar(100))

    insert into @table values ('Apple'),('Melons'),('Orange'),('Mangoes')

    --concattinate

    declare @nsql nvarchar(max)

    set @nsql = ''

    select @nsql = @nsql+' '+ name

    from @table

    select @nsql

Viewing 15 posts - 16 through 30 (of 160 total)

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