我有两个表：表A包含主产品数据,表B包含子产品数据.我想更新表A,以便在它只有一个不同的值时保持表B中相同列的相同值.如果非重要计数大于1,我想用逗号连接列.除了键列名之外,这些表具有相同的列.
是否可以编写匹配所有列并处理合并/连接的动态sql？
示例如下：
表A.
ID | color | Location ____| ______|_____________ 1 | | 2 | | 3 | | 4 | |
表B.
child_ID | parent_id | Color | Location __________|___________|________|_________ 1 | 1 | white | house 2 | 2 | red | garage 3 | 2 | white | garage 4 | 3 | blue | house
表A将如下所示：
ID | color | Location ____| ___________|_____________ 1 | white | house 2 | red,white | garage 3 | white | house
解决方法
检查一下……
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,'I' UNION SELECT 4,3,'blue','IV'
UNION SELECT 5,'I' UNION SELECT 6,'I'
UNION SELECT 7,'gray','I' UNION SELECT 8,'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('''')
),'''' )'
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,IV
*/
