Select field from table A where subselect from table B = table A field

Question

I need a little help getting the results I need. I have 2 tables with a common field of id. I want to combine the values from table B that have the same id

Table A

id | name | somevalue1
1  | dud  | 12345
2  | duda | 8908

Table B

id | somevalue2
1  | 56545
2  | 545665
1  | 89875
2  | 12524

Desired Result

id | somevalue2 combined
1  | 56545, 89875
2  | 545665, 12524

I've tried using a Join, but I'm a bit lost. I have been attempting to do a subselect, but the only way I can think of to make it work is to reference the main query from within the subselect, but that aint gettin' it either.

This query I have been trying just hangs:

select distinct a.id,(
    select b.somevalue2 + ', ' as [text()] from tableB b 
    where b.id = a.id and b.somevalue2 is not null for xml path('')) 
    as [ColumnName]
from tableA a
order by a.id asc

EDIT

I think I need to mention that TableB has over 400,000 rows. TableA has only about 1,500 rows.

Maybe you can find some help in [this other question](http://stackoverflow.com/q/17591490/1385896) — Filipe Silva, Nov 15 '13 at 16:39
I was just wondering if in the results under `Desired Results` is correct. The values in `somevalue2 combined` do not seem to have any values from `Table A` — Squirrel5853, Nov 15 '13 at 16:48
@SecretSquirrel - Yes that is correct. There are multiple values in tableB that I need combined in one row per id number. — ckpepper02, Nov 15 '13 at 16:52
@OGHaza ah yes I misread the question. So `Table A` is really irrelevant in this question. — Squirrel5853, Nov 15 '13 at 16:53

score 3 · Accepted Answer · answered Nov 15 '13 at 16:47

3

This works:

SELECT A.*,
       STUFF((
              SELECT ', ' + CAST([somevalue2] AS VARCHAR(20))
              FROM TableB B
              WHERE A.id = B.id
              FOR XML PATH ('')), 1, 1, '')
FROM TableA A

Here is an sqlfiddle for you to try.

answered Nov 15 '13 at 16:47

Lamak

69,480
12
108
116

Thank you! The query does take a long time to run however. TableB has over 400,000 rows. Is there another way to do this? – ckpepper02 Nov 15 '13 at 17:00
1

@ckpepper02 Not many ways to do a fast group concatenation on SQL Server. Make sure that TableB has an index on `id` (even better if the index include columm `somevalue2`, something like `CREATE INDEX I_test ON TableB(id) INCLUDE(somevalue2);`) – Lamak Nov 15 '13 at 17:04
Thanks again. The query took 14mins to run, but I got exactly what I was looking for so thank you!! – ckpepper02 Nov 15 '13 at 19:15

Select field from table A where subselect from table B = table A field

Table A

Table B

Desired Result

EDIT

1 Answers1