sql – 将两个表/连接值合并为单个列

我有两个表：表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
*/

（编辑：拼字网 - 核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!