首页 文章

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

提问于
浏览 次
1

我有两个表：表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
sql sql-server-2008

3 回答

  • 1

    检查一下......

    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
*/
    回复于
  • 2

    我想这会做到：

    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
    回复于
  • 3

    看看这个问题的答案：

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

    回复于

相关问题