Merge two tables / concatenate values into single column
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 on开发者_StackOverflow社区ly 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
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
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
*/
See the answers to this question:
how-to-return-multiple-values-in-one-column
精彩评论