Returning a column in a result set from merging results in another table

Question

Please forgive the title, I wasn't sure how exactly to describe the situation below...

I have 2 tables. One column in the first table has a set of comma delimited codes that are in a second table. So, the two tables look like this:

Table1

RepID | RepDate  | RepLocation
  1     1/1/2010   BH,,,,AH,,,
  2     2/1/2010   ,,,,,AH,,,

Table2

LocID | LocName
BH      Bliss Hall
AH      Agans Hall

I can successfully select from both tables using joins, and I obviously get multiple rows in the resultset:

RepID | RepDate  | RepLocation
  1     1/1/2010   Bliss Hall
  1     1/1/2010   Agans Hall
  2     2/1/2010   Agans Hall

But what I'd really like to do is get a result that looks like this:

RepID | RepDate  | AllRepLocations
  1     1/1/2010   Bliss Hall Agans Hall
  2     2/1/2010   Agans Hall

I've never tried to do this before, and I'm having trouble coming up with the T-SQL to get this result, if it is even possible. I am calling a stored procedure, so if I need to do some extra coding or machinations to get the result I want, it is not a problem as I can do them in the stored procedure. This is on SQL Server 2008 R2.

Thank you.

possible duplicate of [Concatenating Column Values into a Comma-Separated List](http://stackoverflow.com/questions/1048209/concatenating-column-values-into-a-comma-separated-list) — Andrew, May 19 '15 at 16:41
possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Tab Alleman, May 19 '15 at 16:41
@M.C. Please review the possible duplicates mentioned in the comments and if your question is different then update it explaining why. — John Odom, May 19 '15 at 16:46

Stephan · Accepted Answer · 2015-05-19T17:52:19.810

0

Okay I think your best bet is to use dynamic SQL with REPLACE(). Try this out:

Your Table

CREATE TABLE Table1 (RepID INT,RepDate DATE,RepLocation VARCHAR(100));
INSERT INTO Table1
VALUES  (1,'20100101','BH,AH'),
        (2,'20100201','AH');

CREATE TABLE Table2 (LocID CHAR(2),LocName VARCHAR(25));
INSERT INTO Table2
VALUES  ('BH','Bliss Hall'),
        ('AH','Agans Hall');

Actual Query

DECLARE @Replace VARCHAR(MAX);

SELECT @Replace = COALESCE('REPLACE( ' + @Replace,'REPLACE(RepLocation + '',''') + ',''' + LocId + ','',''' + LocName + ' '')'
FROM Table2

EXEC
(
'SELECT RepID,RepDate,' + @Replace + ' AS AllRepLocations
FROM Table1' --Change Table1 to your actual tableName
)

Results:

RepID       RepDate      AllRepLocations
----------- ----------   -------------------------
1           2010-01-01   Bliss Hall Agans Hall
2           2010-02-01   Agans Hall

edited May 19 '15 at 17:52

answered May 19 '15 at 17:08

Stephan

5,891
1
16
24

Thank you. The solution you provided works, basically, except that the data I am working with makes it do one odd thing. I see that it is doing a nested REPLACE( REPLACE (REPLACE ( ....... ))). What is happening is that one of the inner replaces is messing up an outer replace. I have codes 'CE' for 'Colby/Elson Hall', and 'SO' for 'Seaton Hall' in Table2. If I have code CE in Table1, the resulting group of REPLACEs in the answer gives me the AllRepLocations of 'Colby/ElSeaton Halln Hall', I.E. within all the replaces, it is finding the 'SO' in 'Elson' and replacing it with 'Seaton Hall'. – M.C. May 19 '15 at 17:45
I should have thought of that. Try my edited code. It checks for a comma next to teh LocID. It should work now. – Stephan May 19 '15 at 17:53
1

Thank you, sir, this seems like exactly what I needed. – M.C. May 19 '15 at 18:01

Returning a column in a result set from merging results in another table

1 Answers1

Your Table

Actual Query