SQL Server Performance

Joining together string fields

Discussion in 'General Developer Questions' started by jiminy, Aug 25, 2003.

  1. jiminy New Member

    Hi,
    Without using a cursor I need to be able to write a query that will return a text value that contains all the entries in a column appended together for each id. The number of entries per id is not limited.

    e.g table X with field tID and tText.
    containing
    1,'Text 1'
    1,'Text 2'
    1,'Text 3'
    2,'Text 1'
    2,'Text 2'
    3,'Text 1'
    3,'Text 2'
    3,'Text 3'
    3,'Text 4'
    4,'Text 1'

    I want the result to be

    1,'Text 1,Text 2,Text 3'
    2,'Text 1,Text 2'
    3,'Text 1,Text 2,Text 3,'Text 4'
    4,'Text 4'


    Any help would be appreciated.

    Regards
    Jiminy



  2. jasper_smith New Member

    Have a look at <a href='http://tinyurl.com/l62h' target='_blank' title='http://tinyurl.com/l62h'<a target="_blank" href=http://tinyurl.com/l62h>http://tinyurl.com/l62h</a></a> as there is some good detail as to why the COALESCE 'trick' that is often recomended in response to such a request has some shortcomings (plus you'll find an answer to your question [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]). Having said that, I have used it in production code in limited cases with no apparent side effects but test,test,test. If you search google groups for "COALESCE csv" you'll get plenty of hits<br /><br /><br />HTH<br /><br />Jasper Smith
  3. SanetteWessels New Member

    Here is some sample code:


    declare @tbl_Test TABLE (ID INT, Text VARCHAR(10))
    declare @tbl_New TABLE (ID INT, Text VARCHAR(1000))
    declare @cItemList varchar(8000)

    INSERT INTO @tbl_Test VALUES(1,'Text 1')
    INSERT INTO @tbl_Test VALUES(1,'Text 2')
    INSERT INTO @tbl_Test VALUES(1,'Text 3')
    INSERT INTO @tbl_Test VALUES(2,'Text 1')
    INSERT INTO @tbl_Test VALUES(2,'Text 2')
    INSERT INTO @tbl_Test VALUES(3,'Text 1')
    INSERT INTO @tbl_Test VALUES(3,'Text 2')
    INSERT INTO @tbl_Test VALUES(3,'Text 3')
    INSERT INTO @tbl_Test VALUES(3,'Text 4')
    INSERT INTO @tbl_Test VALUES(4,'Text 1')

    DECLARE @Min INT, @Max INT
    select @Min = min(ID) from @tbl_Test
    select @Max = max(ID) from @tbl_Test

    while @Min <= @Max
    begin
    IF EXISTS(SELECT ID FROM @tbl_Test WHERE ID = @Min)
    BEGIN
    SELECT @cItemList = COALESCE(@cItemList + ',' , '') + CAST(Text AS varchar(10))
    FROM @tbl_Test
    WHERE ID = @Min

    INSERT INTO @tbl_New
    VALUES (@Min,@cItemList)
    END

    SET @Min = @Min + 1
    SET @cItemList = NULL

    END

    SELECT * from @tbl_New

    Sanette

    SQL Developer
    JustEnough Software Corporation

    quote:Walking on water and coding of a spec is easy, as long as both are frozen - A friend

Share This Page