Concatenating Rows

  • Another one CTE solution:

    with

    fruit ([value], )as (

    select cast('Apple' as nvarchar(max)) , 101 union all

    select 'Banana' , 102 union all

    select 'Orange' , 103 union all

    select 'Melon' , 104 union all

    select 'Grape' , 105

    ),

    list as

    (

    select , [value]

    from fruit

    where = 101

    union all

    select f1., list.[value] + ', ' + f1.[value]

    from fruit f1

    inner join list on f1. = list. + 1

    )

    select top 1 list.[value]

    from list

    order by list. desc

  • what about this query->select Name+',' from Fruit Order By Id for xml path('')

  • What I have never been able to figure out is why the SQL team has never implemented a standard built in aggregate function called ConcatenateToCSV or something.

  • Here is alternative and the performance should be a little better as well:

    Declare @locBclCodeList Varchar(Max)

    Select @locBclCodeList = COALESCE(LTrim(RTrim(@locBclCodeList)) + ',' ,'') + [BclCode]

    From dbo.BclCodes

    Print ' @locBclCodeList [' + @locBclCodeList + ']'

    will creat a comma delimited list from a table of the BclCodes from each record in dbo.BclCodes.

    dbo.BclCodes contains:

    BclCodeId Int.

    BclCOde Varchar(10),

    MainBclCode Varchar(10),

    PrService Bit,

    HrService Bit,

    TcService Bit,

    ElecRept Bit,

    Several other Bit fields.

    I used this code to create a comma delimited list that I can use to select clients from other tables:

    Where (@locBclCodeList = '*' Or

    tblEarnings.BclCode In (Select Word

    From dbo.udfGetWord(@locBclCodeList, ',')

    Where Word = tblEarnings.BclCode))

    dbo.udfGetWord is a udf that will quickly search a delimit list of values.

    -- =========================================================================

    --Source:udfGetWord

    --Version:1.0.0

    --Author:R Haverty

    --Date:08/30/2008

    --Revised:

    --

    --Function:This udf was copied from GetAllWords2 and modified to return

    --the Word Trimmed and in Upper Case.

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

    -- =========================================================================

    Use TARS

    GO

    -- =========================================================================

    IF OBJECT_ID ('dbo.udfGetWord') IS NOT NULL

    DROP Function dbo.udfGetWord

    GO

    -- ==================================================================== =======

    --Object: UserDefinedFunction [dbo].[GETALLWORDS2]

    --Script Date: 08/30/2008 14:10:22

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    --Author: Igor Nikiforov, Montreal, EMail: udfunctions@gmail.com

    --GETALLWORDS2() User-Defined Function Inserts the words from a string into

    --the table.

    --GETALLWORDS2(@cString[, @cStringSplitting])

    --Parameters

    --*)@cString nvarchar(4000)- Specifies the string whose words will be

    --inserted into the table @GETALLWORDS2.

    --*)@cStringSplitting nvarchar(256) - Optional. Specifies the string used to

    --separate words in @cString.

    --The default delimiter is space.

    --Note that GETALLWORDS2( ) uses @cStringSplitting as a single delimiter.

    --Return Value table

    --Remarks GETALLWORDS2() by default assumes that words are delimited by space.

    --If you specify another string as delimiter, this function ignores spaces and

    --uses only the specified string.

    --

    -- Example

    --*)Declare @cString nvarchar(4000), @nIndex smallint

    --Select @cString = 'We hold these truths to be self-evident,

    --that all men are created equal, that they are

    --endowed by their Creator with certain unalienable Rights,

    --that among these are Life, Liberty and the pursuit of

    --Happiness.', @nIndex = 30

    --Select WORD from dbo.GETALLWORDS2(@cString, default) where WORDNUM = @nIndex -- Displays 'Liberty'

    --Select top 1 WORDNUM from dbo.GETALLWORDS2(@cString, default) order by WORDNUM desc -- Displays 35

    --See Also GETWORDNUM() , GETWORDCOUNT() , GETALLWORDS() User-Defined Functions

    --If no break string is specified, the function uses space to delimit words.

    CREATE function [dbo].[udfGetWord]

    (

    @cStringnvarchar(4000),

    @cStringSplittingnvarchar(256) = ' '

    )

    Returns @tblGetAllWords table

    (

    WORDNUMsmallint,

    WORDnvarchar(4000),

    STARTOFWORDsmallint,

    LENGTHOFWORDsmallint

    )

    --===========================================================================

    Begin--udfGetWord

    --=========================================================================

    Declare @ksmallint,

    @BegOfWordsmallint,

    @wordcountsmallint,

    @nEndStringsmallint,

    @nLenSrtingSplittingsmallint,

    @flagbit

    --=========================================================================

    Select@cStringSplitting = isnull(@cStringSplitting, space(1)) ,

    @cString = isnull(@cString, '') ,

    @BegOfWord = 1,

    @wordcount = 1,

    @k = 0 ,

    @flag = 0,

    @nEndString = 1 + datalength(@cString) / (case SQL_VARIANT_PROPERTY(@cString,'BaseType')

    When'nvarchar' then 2 else 1 end),

    @nLenSrtingSplitting = datalength(@cStringSplitting) /(case SQL_VARIANT_PROPERTY(@cStringSplitting,'BaseType')

    When'nvarchar' then 2 else 1 end) -- for unicode

    --=========================================================================

    While 1 > 0

    Begin--while 1 > 0

    If @k - @BegOfWord > 0

    Begin--If @k - @BegOfWord > 0

    Insert into @tblGetAllWords(WORDNUM,

    Word,

    STARTOFWORD,

    LENGTHOFWORD)

    Values(@wordcount,

    Upper(LTrim(RTrim(substring(@cString,

    @BegOfWord ,

    @k - @BegOfWord)))),

    @BegOfWord,

    @k - @BegOfWord)

    Select@wordcount = @wordcount + 1, @BegOfWord = @k

    End--If @k - @BegOfWord > 0

    If @flag = 1

    Break

    While Charindex( Substring(@cString, @BegOfWord, @nLenSrtingSplitting)

    COLLATE Latin1_General_BIN, @cStringSplitting COLLATE Latin1_General_BIN) > 0 -- skip break strings, if any

    Set @BegOfWord = @BegOfWord + @nLenSrtingSplitting

    select@k = Charindex(@cStringSplitting COLLATE Latin1_General_BIN,

    @cString COLLATE Latin1_General_BIN,

    @BegOfWord)

    If @k = 0

    select @k = @nEndString, @flag = 1

    End--while 1 > 0

    --===========================================================================

    Return

    End--udfGetWord

  • A CTE way of doing it

    -- Create a variable as table

    DECLARE @Fruit TABLE (

    ID INT,

    Name varchar(25)

    )

    -- Populate the table with sample data

    INSERT INTO @Fruit(ID, Name)

    VALUES

    (101,'Apple')

    ,(102,'Banana')

    ,(103,'Orange')

    ,(104,'Melon')

    ,(105,'Grape')

    -- Lets us create two cte's

    ;WITH fruit(row, Name)

    -- fruit is just a sequencially numbered list of names

    -- When using ROW_NUMBER we eliminate IDs out of sequence

    AS

    (

    -- ROW_NUMBER() gives us a sequencial list

    -- OVER (ORDER BY (SELECT NULL)) order by NULL, low cost order by

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CAST(Name as varchar(max)) FROM @fruit

    ), fruits(row, String)

    AS (

    -- Get the first row in the numbered list of names

    SELECT row, Name FROM fruit

    -- Get the lowest rownumber

    WHERE row = (SELECT MIN(row) FROM fruit)

    -- This is the magic

    UNION ALL

    -- Let us begin being recursive

    SELECT

    -- Fetched rownumber

    f2.row,

    -- Concatenate previous row with fetched row

    f1.String + ', ' + f2.Name

    -- This cte

    FROM fruits f1

    -- Previous cte, the numbered list of names

    INNER JOIN fruit f2

    -- Next row

    ON f1.row + 1 = f2.row

    )

    -- Ready to return data

    SELECT String FROM fruits

    -- To get the last row

    WHERE row = (SELECT MAX(row) FROM fruits)

    What IF we had CONCAT as an aggregate function...

    But hey, we can.... SQL CLR is the way to go...

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    using System.Text;

    [Serializable]

    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(

    Format.UserDefined,

    IsInvariantToOrder = false,

    IsInvariantToNulls = true,

    IsInvariantToDuplicates = false,

    MaxByteSize = -1

    )]

    public struct concat :IBinarySerialize

    {

    private StringBuilder _accumulator;

    private string _delimiter;

    public Boolean IsNull { get; private set; }

    public void Init()

    {

    _accumulator = new StringBuilder();

    _delimiter = string.Empty;

    this.IsNull = true;

    }

    public void Accumulate(SqlString Value, SqlString Delimiter)

    {

    if (!Delimiter.IsNull

    & Delimiter.Value.Length > 0)

    {

    _delimiter = Delimiter.Value;

    if (_accumulator.Length > 0) _accumulator.Append(Delimiter.Value);

    }

    _accumulator.Append(Value.Value);

    if (Value.IsNull == false) this.IsNull = false;

    }

    public void Merge(concat Group)

    {

    if (_accumulator.Length > 0

    & Group._accumulator.Length > 0) _accumulator.Append(_delimiter);

    _accumulator.Append(Group._accumulator.ToString());

    }

    public SqlString Terminate()

    {

    // Put your code here

    return new SqlString(_accumulator.ToString());

    }

    void IBinarySerialize.Read(System.IO.BinaryReader r)

    {

    _delimiter = r.ReadString();

    _accumulator = new StringBuilder(r.ReadString());

    if (_accumulator.Length != 0) this.IsNull = false;

    }

    void IBinarySerialize.Write(System.IO.BinaryWriter w)

    {

    w.Write(_delimiter);

    w.Write(_accumulator.ToString());

    }

    }

    But I'm sorry for not reading through the thread... All above already said...

    Besides the little trickster in the cte I did with ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    The xml-way is the fastest performer compared to SQL CLR, about ten times faster on a table of 75000 words, and about 500 times faster than the cte-method, and some 1000 times faster than the variable-method

  • It is a shame that TSQL lacks the CONNECT BY clause that is available with Oracle. All these complex solutions could be eliminated for many classes of row-joining queries.

  • SQL2005 and higher have the coalesce command.

    declare @remaining_string varchar(max)

    set @remaining_string = ''

    select @remaining_string = coalesce (@remaining_string + db.name + char(44), '')

    from database db (nolock)

    where db.id = X

    order by db.name

    At the end @remaining_string = 'name1,name2,name3,...,nameN'

  • Robert Bourdeau (3/4/2011)


    It is a shame that TSQL lacks the CONNECT BY clause that is available with Oracle. All these complex solutions could be eliminated for many classes of row-joining queries.

    I'll second that. I've been using the CONNECT BY for over 20 years. Can't live without it. Consequently, I have to re-invent it in some shape, matter, or form in SQL Server.


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • I think this (using a cursor to iterate on the list) is the most reasonable solution for the newbie sql dveloper.

    Is there a performance issue in this way ?

    I think it shuold beat the recursion any time, and I'm not sure about the other one (xml path)

    Any insights ?

  • My solution where I posted the coalesce solution will eat up a cursor on performance. Another better solution than using a cursor is:

    Declare @tblName Table

    (

    tblNameId Int Identity(1,1),

    BclCode Varchar(10),

    )

    Declare @locMaxtblNameId Int,

    @locBclCode Varchar(10),

    @locRecdNo Int

    -- Get all the Client Numbers

    Insert Into @tblName

    Select BclCode

    From dbo.Client_Main

    Order By BclCode

    Set @locMaxtblNameId = @@Identity

    Set @locRecNo = 1

    While tblNameId <= @locMaxtblNameId

    Begin

    Select @locBclCode = BclCode

    From @tblName

    Where tblNameId = @locRecNo

    Print 'BclCode selected was [' + @locBclCde & ']'

    -- Reduce the table size if you don't need the records again

    Delete @tblName

    Where tblNameId = @locRecNo

    Set @locRecNo = @locRecNo + 1

    End

    -- This works even better in a stored proc where you can create an index for

    -- tblNamId and will eat a cursor up in performance

  • Sorry Guys, as I remember the CTE recursion works with depth <= 70

  • Hi guys,

    why dont we keep it simple?

    CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)

    INSERT INTO #test ( id, name )

    VALUES ( 101, 'Apple' ),

    ( 102, 'Banana' ),

    ( 103, 'Orange' ),

    ( 104, 'Melon' ),

    ( 105, 'Grape' )

    SELECT * FROM #test

    DECLARE @fruit VARCHAR(MAX)

    SELECT @fruit = COALESCE(@fruit + '', '') + name

    FROM #test

    SELECT Colors = @fruit

    It take no efford at all.

    Artur

  • Just curious as to why you didn't consider the built-in T-SQL function called COALESCE. I often need to do what you describe and have found that COALESCE is relatively simple code and performs well.

    I use a function similar to the following:

    CREATE FUNCTION dbo.udf_Get_FG_Warehouse_List (@DocumentID int)

    RETURNS varchar(1000)

    AS

    BEGIN

    DECLARE @Warehouse_List varchar(1000)

    SELECT

    @Warehouse_List = COALESCE(@Warehouse_List + ', ', '') + a.Name

    FROM

    Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.ID

    WHERE

    b.NPFID = @DocumentID

    ORDER BY

    a.Name

    RETURN

    @Warehouse_List

    END

  • arturv (3/4/2011)


    Hi guys,

    why dont we keep it simple?

    CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)

    INSERT INTO #test ( id, name )

    VALUES ( 101, 'Apple' ),

    ( 102, 'Banana' ),

    ( 103, 'Orange' ),

    ( 104, 'Melon' ),

    ( 105, 'Grape' )

    SELECT * FROM #test

    DECLARE @fruit VARCHAR(MAX)

    SELECT @fruit = COALESCE(@fruit + '', '') + name

    FROM #test

    SELECT Colors = @fruit

    It take no efford at all.

    Artur

    This is what I was thinking, and it what was several others already mentioned. Here is a "WITH" version of that one from Artur:

    DECLARE @joined VARCHAR(max);

    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 @joined=COALESCE(@joined+',', '')+name FROM fruit

    SELECT @joined

  • peter.stanford 73369 (3/4/2011)


    Just curious as to why you didn't consider the built-in T-SQL function called COALESCE. I often need to do what you describe and have found that COALESCE is relatively simple code and performs well.

    I use a function similar to the following:

    CREATE FUNCTION dbo.udf_Get_FG_Warehouse_List (@DocumentID int)

    RETURNS varchar(1000)

    AS

    BEGIN

    DECLARE @Warehouse_List varchar(1000)

    SELECT

    @Warehouse_List = COALESCE(@Warehouse_List + ', ', '') + a.Name

    FROM

    Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.ID

    WHERE

    b.NPFID = @DocumentID

    ORDER BY

    a.Name

    RETURN

    @Warehouse_List

    END

    That works fine but is a form of RBAR because the function is necessarily a scalar function which refers to a table. That can make performance actually worse than a cursor.

    --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.

    Change is inevitable... Change for the better is not.


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

Viewing 15 posts - 121 through 135 (of 159 total)

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