Combining specific sql query results into one string

Question

I have the following query;

SELECT custom.name, instance_id 
FROM table1 tb 
WHERE tb.instance_id = 1111 OR tb.instance_id = 2222

This returns the following results;

test, 1111
test1, 1111
test3, 1111
tabletest, 2222
tabletest1, 2222
tabletest2, 2222

I would like the ability to match the instances_id, and combine the matching rows into a single string.

i.e.

test;test1;test3
tabletest;tabletest1;tabletest2

I can get a single string but at the moment this grabs all the results and puts it into a single string.

STUFF((
SELECT custom.name + ';'
FROM table1 tb 
WHERE tb.instance_id = 1111 OR tb.instance_id = 222
FOR XML PATH(' '), TYPE.value('.', 'NVARCHAR(MAX)'), 1, 0, ' ')

this results in

test;test1;test3;tabletest;tabletest1;tabletest2

Unfortunately I cannot upgrade past sql server version 15 which perhaps limits me.

What do you mean by "SQL Server version 15" ? If you're talking about the program version - then "v15" is SQL Server 2019 which is the absolutely newest version available on the market - of course you cannot upgrade past this version - there is no newer version at all ! — marc_s
– marc_s, Commented Sep 14, 2020 at 12:07

Gordon Linoff · Accepted Answer · 2020-09-14 12:07:00Z

1

You need a correlation clause in the subquery. I would suggest:

SELECT v.instance_id,
       STUFF((SELECT ';' + tb.name 
              FROM table1 tb 
              WHERE tb.instance_id = v.instance_id
              FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)'
                    ), 1, 1, ' '
            )
FROM (VALUES (1111), (2222)) v(instance_id);

Here is a db<>fiddle.

answered Sep 14, 2020 at 12:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SteveC · Accepted Answer · 2020-09-14 12:15:54Z

1

Data

drop table if exists dbo.tTable;
go
create table dbo.tTable(
  [name]                varchar(100) not null,
  instance_id           int not null);

insert dbo.tTable values
('test',1111),
('test1',1111),
('test3',1111),
('test',2222),
('test1',2222),
('test2',2222);

Query

select instance_id, 
      stuff((select ';' + cast([name] as varchar(100))
             from tTable c2
             where t.instance_id = c2.instance_id
             order by [name] FOR XML PATH('')), 1, 1, '') [value1]
from tTable t
group by instance_id;

Output

instance_id value1
1111        test;test1;test3
2222        test;test1;test2

answered Sep 14, 2020 at 12:15

SteveC

6,0252 gold badges14 silver badges26 bronze badges

Collectives™ on Stack Overflow

Combining specific sql query results into one string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related