Merge two tables / concatenate values into single column

Question

I have two tables: table A holds master product data, and table B holds children product data. I would like to update table A to hold the same value of the identical columns in table B when it only has one distinct value. If the distinct count is great than one, I would like to concatenate the columns with a comma. The tables have the same columns except the key column name.

Is it possible to write dynamic SQL that will match up all the columns and handle the merge / concatenation?

Example below:

Table A

ID  | color | Location
____| ______|_____________
1   |       | 
2   |       | 
3   |       | 
4   |       |

Table B

child_ID  | parent_id | Color  | Location
__________|___________|________|_________
1         |   1       | white  | house
2         |   2       | red    | garage
3         |   2       | white  | garage
4         |   3       | blue   | house

Table A will look like:


ID  |    color   | Location
____| ___________|_____________
1   | white      | house 
2   | red, white | garage
3   | white      | house

Is there a reason you want to store multiple values in one field? It will make it a little slower to search for particular records. — , May 18 '11 at 00:17

score 4 · Answer 1 · answered May 18 '11 at 01:08

4

I think this will do it:

SELECT parent_id AS ID, 
  STUFF((
    SELECT ','+color FROM TableB a WHERE a.parent_id = b.parent_id FOR XML PATH('')
  ),1,1,'') AS color,
  STUFF((
    SELECT ','+Location FROM TableB a WHERE a.parent_id = b.parent_id FOR XML PATH('')
  ),1,1,'') AS Location,
FROM TableB b
GROUP BY parent_id

answered May 18 '11 at 01:08

ic3b3rg

14,629
4
30
53

would work on a per column basis, but I need it to be dynamic to do the work for all columns. – Tom May 18 '11 at 03:30

score 2 · Accepted Answer · answered May 18 '11 at 16:02

Check this...

IF OBJECT_ID('TableA') IS NOT NULL DROP TABLE TableA
IF OBJECT_ID('TableB') IS NOT NULL DROP TABLE TableB
CREATE TABLE TableA (ID  INT,  Color  VARCHAR(max), Location VARCHAR(max), Class VARCHAR(max))
CREATE TABLE TableB (child_ID  INT,  parent_ID INT,  Color  VARCHAR(10), Location VARCHAR(10), Class VARCHAR(10))
INSERT INTO TableB 
      SELECT 1,1,'white','house'  ,'I' UNION SELECT 2,2,'red'  ,'garage' ,'II'
UNION SELECT 3,2,'white','garage' ,'I' UNION SELECT 4,3,'blue' ,'house'  ,'IV'
UNION SELECT 5,3,'blue' ,'garage' ,'I' UNION SELECT 6,3,'white','garage' ,'I'
UNION SELECT 7,3,'gray' ,'garage' ,'I' UNION SELECT 8,2,'gray' ,'house'  ,'IV'

SELECT * FROM TableB

DECLARE @cmd VARCHAR(max);
SET @cmd = 'INSERT INTO TableA SELECT ID = b.parent_id '
SELECT @cmd = @cmd + ' , ['+COLUMN_NAME+'] = STUFF(
            (   SELECT '', '' +'+COLUMN_NAME+' 
                FROM TableB 
                WHERE parent_id = b.parent_id
                GROUP BY '+COLUMN_NAME+' 
                FOR XML PATH('''') 
            ) , 1, 2, '''' )'
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='TableB' AND COLUMN_NAME NOT IN ('child_ID', 'parent_id')

SELECT @cmd = @cmd + ' FROM TableB AS b GROUP BY b.parent_id'

EXEC(@cmd)
SELECT * FROM TableA

/* -- OUTPUT
  ID | Color             | Location      | Class
  ---------------------------------------------------
  1  | white             | house         | I
  2  | gray, red, white  | garage, house | I, II, IV
  3  | blue, gray, white | garage, house | I, IV
*/

thanks for your help. i hope this helps others in the future. — Tom, May 18 '11 at 16:59

score 1 · Answer 3 · edited May 23 '17 at 11:47

1

See the answers to this question:

how-to-return-multiple-values-in-one-column

edited May 23 '17 at 11:47

Community

1
1

answered May 18 '11 at 01:55

leoinfo

7,860
8
36
48

again this would work but i need it to be dynamic to work on all columns within a table. – Tom May 18 '11 at 12:36
@chipShot - try to ease the work of ones that are trying to help you by providing a sql script that can actually be used to create example tables and provide the expected output based on those tables. I think I can help here, but I don't want to spend time on a question that I did not fully understand. – leoinfo May 18 '11 at 15:33

Merge two tables / concatenate values into single column

3 Answers3

Linked