Combining numerous strings into one row

Question

I'm having the following problem.

My sample data looks as following:

03D 03F 03G 03H 03I 04E 05D 05G 05H 06C 08D 08D (BEST) 08E 08E (BEST) 08F 08F (BEST) 08G 08G (BEST) 08H 08H (BEST) 08I 08I (BEST) 08J 08K 08k08K 08L 08L (BEST)

I need to combine this data into one string that will looks like the following:

03D;03F;03G;03H;03I;04E;05D;05G;05H;06C;08D;08D (BEST);08E;08E (BEST);08F;08F (BEST);08G;08G (BEST);08H;08H (BEST);08I;08I (BEST);08J;08K;08k08K;08L;08L (BEST);

Note that there will always be data added to this table, therefore, I would need this to run accurately, to update a single field using the results of this.

The different values should be split with this operator ;

The script I tried to achieve this is:

declare @loop   int
,       @Tempid int
,       @lookup varchar(max)

    declare @bin table
    (id  int identity primary key, bin varchar(100))

    insert into @bin (bin)
    select distinct
    cBinLocationName
    from    _btblBinLocation

    set @lookup =   ''
                begin
                    select @Loop = min(ID) FROM @bin
                    while @Loop IS NOT NULL
                        begin
                        set @Tempid =   (select id from @bin where id=@Loop)
                            set @lookup =   @lookup + (select bin FROM @bin where ID=@Tempid)+';'
                            select @Loop = min(ID) FROM @bin where ID>@Loop
                        end
                end
    select  @lookup

The problem is, my results is '' the whole time.

Please help?

I don't think so, due to the data extract would be out of a SQL Table — Attie Wagner, Oct 17 '18 at 11:48
It's coming out of a SQL Table, hence my code is `select distinct cBinLocationName from _btblBinLocation` — Attie Wagner, Oct 17 '18 at 11:49
Don't be put off by the use of the `FOR XML` directive in the duplicate @Larnu linked, it's retrieving the data from a database table and concatenating it using a delimiter — Diado, Oct 17 '18 at 11:52
Thanks Larnu, yes, your assistance helped. And thanks Diado, I've never used XML in this regard, I don't understand exactly how it works, but I managed to get it to work, I'll share the answer now. — Attie Wagner, Oct 17 '18 at 11:59

score 0 · Accepted Answer · answered Oct 17 '18 at 12:01

I resolved it by navigating to the link that Larnu posted in the comments.

I altered the answer in there to work for me and below is the script:

select top 1  stuff((select distinct cBinLocationName + ';'
            from _btblBINLocation
        for xml path('')), 1, 1, '') as lookup
from _btblBINLocation

This does exactly what I want. With these results, I will be able to update a field based on this.

Thanks everyone.

The results looks like this:

No need for the TOP 1 or second FROM here. – Thom A Oct 17 '18 at 12:08 — Thom A, Oct 17 '18 at 12:08

score 0 · Answer 2 · answered Oct 17 '18 at 13:53

Here's a simple example without the need for XML and STUFF:

DECLARE @TestData TABLE
    (
        [TestData] NVARCHAR(200)
    );

DECLARE @Del CHAR(1) = ';';
DECLARE @Lookup NVARCHAR(MAX) = '';

INSERT INTO @TestData (
                          [TestData]
                      )
VALUES ( '03D' )
     , ( '03F' )
     , ( '03G' )
     , ( '03H' )
     , ( '03I' )
     , ( '04E' )
     , ( '05D' )
     , ( '05G' )
     , ( '05H' )
     , ( '06C' )
     , ( '08D' )
     , ( '08D (BEST)' )
     , ( '08E' )
     , ( '08E (BEST)' )
     , ( '08F' )
     , ( '08F (BEST)' )
     , ( '08G' )
     , ( '08G (BEST)' )
     , ( '08H' )
     , ( '08H (BEST)' )
     , ( '08I' )
     , ( '08I (BEST)' )
     , ( '08J' )
     , ( '08K' )
     , ( '08k08K' )
     , ( '08L' )
     , ( '08L (BEST)' );

--puts the result set into @Lookup
SELECT @Lookup = @Lookup + [TestData] + @Del
FROM   @TestData;

--Remove the trailing delimiter
SET @Lookup = SUBSTRING(@Lookup, 1, LEN(@Lookup) - 1);

SELECT @Lookup;

Combining numerous strings into one row

2 Answers2