Combine two tables / merge values ​​into one column

Question

I have two tables: table A contains data on the main products, and table B contains data on the subsidiary products. I would like to update table A to have the same value for the same columns in table B when it has only one different value. If a single counter is superior to one, I would like to combine the columns with a comma. Tables have the same columns except the column name.

Is it possible to write dynamic SQL that will fit all columns and handle 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 this:

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

Answer 1

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 */ 

Answer 2

I think this will do:

 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 

Answer 3

See the answers to this question:

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