Joining multiple row results, from query, into 1 column with scalable values

Question

I'm trying to figure out, how to combine multiple rows, using scalable values, into comma separated columns based on unique ID´s.

I have these 2 test values 1111, 2222 from db.master, but the values could be many others.

They each have multiple different group ids associated in this table

[Meterdb].[dbo].[Group]

Serial groupId
1111    1
1111    2
1111    3
1111    4
2222    5
2222    6
2222    7
2222    8

This is my desired result

Serial  groups
1111    1,2,3,4
2222    5,6,7,8

I have gotten it to work with a single value, however as soon as I insert multiple values it only displays the first result.

I clearly have flaws in the code, maybe in the structure itself, but I've been unable to figure out how to get it working scalable values.

To get the comma separated results I've been using the STUFF function.

So what I'm actually trying to achieve, is how to properly get the stuff command, to only return values that are = to the serial I'm searching for.

If i don't insert my declared value into the stuff command, it just returns all values from the group column not associated with the serial column. I've also tried joining the STUFF function as a sub-query, but with the same results.

DECLARE @tempD TABLE  (list VARCHAR(25))
insert into @tempD
values ('1111') ,('2222')

Select serial, groups =  

( STUFF (( SELECT ',' +convert (varchar(30),GroupId) 

from master md  
left outer join [Meterdb].[dbo].[Group] rg on md.serial = rg.[serial] 

WHERE serial in ( select list from @tempD )
FOR XML PATH ('')) ,1 ,1, ''))
from  master
WHERE serial in ( select list from @tempD)

I except

serial  groups
1111    1,2,3,4
2222    5,6,7,8

Result:

serial  groups
1111    1,2,3,4

How do I get the function to only return the groups that are = to the serial, in such a way, that I can insert multiple variables.

You need to turn your subquery into a correlated subquery. Possible duplicate of [SQL Server : GROUP BY clause to get comma-separated values](https://stackoverflow.com/questions/12668528/sql-server-group-by-clause-to-get-comma-separated-values)? — Thom A, Jul 15 '19 at 13:56
By inspecting your query I would expect a different output than the one you are getting. You mention the table `[Meterdb].[dbo].[Group]` but your query has a `select ... from master`. What does the `master` table contain? — George Menoutis, Jul 15 '19 at 14:01
Well its Meterdb.dbo.master it contains the serials along with other columns related to each serial. The joining between the 2 tables requires a unique id, but i thought it was irrelevant. The actual join im using is left outer join [MeterDB.dbo.Meter] me on md.serial = me.id_Meter left outer join [MeterDB].[dbo].[Group] rg on me.Id_Meter = rg.[Id_Meter] — MIchael Lander, Jul 15 '19 at 14:04
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Serg, Jul 15 '19 at 14:05

score 0 · Answer 1 · answered Jul 16 '19 at 14:57

maybe more like that (obviously untested)

DECLARE @tempD TABLE  (list VARCHAR(25));
INSERT INTOl @tempD   values ('1111') ,('2222');

SELECT      serial, 
            STUFF (G.Groups,1,1,SPACE(0))                       AS groups
FROM        master                                              AS MD
OUTER APPLY(SELECT( SELECT ',' +CONVERT(VARCHAR(30),GroupId) 
                    FROM   [Meterdb].[dbo].[Group]              AS rg 
                    WHERE  md.serial = rg.[serial]
                    FOR XML PATH(''), TYPE
                  ).value ('text()[1]','varchar(max)')
            )                                                   AS G(groups)
WHERE       MD.serial IN ( SELECT list FROM @tempD)

Joining multiple row results, from query, into 1 column with scalable values

1 Answers1