SQL Server 2012组列成一个

Question

i有一个表HL_A，该表在一对一的关系中映射到HL_B(HL_A.ID = HL_B.ID(。然后HL_B在一对一(HL_B.ValudID = HL_C.ID(关系中映射到HL_C。

如下所示：

HL_A
╔════╤═════════════╗
║ ID │ ProductType ║
╠════╪═════════════╣
║ 1  │             ║
╚════╧═════════════╝
HL_B
╔════╤═════════╗
║ ID │ ValueID ║
╠════╪═════════╣
║ 1  │ 1       ║
╟────┼─────────╢
║ 1  │ 2       ║
╟────┼─────────╢
║ 1  │ 3       ║
╚════╧═════════╝
HL_C
╔════╤═════════╗
║ ID │ Product ║
╠════╪═════════╣
║ 1  │ A       ║
╟────┼─────────╢
║ 2  │ B       ║
╟────┼─────────╢
║ 3  │ C       ║
╚════╧═════════╝

我想将HL_A.ProductType更新为HL_C.Product列的串联。如下：

╔════╤═════════════╗
║ ID │ ProductType ║
╠════╪═════════════╣
║ 1  │ A, B, C     ║
╚════╧═════════════╝

但是我的查询只会产生一个值：

Update a
SET a.ProductType = STUFF((
        SELECT ', ' + CAST(c.Producte AS VARCHAR(255))
        FROM HL_C c
        WHERE c.ID = b.ValueId
        FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,2,'')
FROM HL_A a
JOIN HL_B b
     ON a.ID = b.ID;

输出：

╔════╤═════════════╗
║ ID │ ProductType ║
╠════╪═════════════╣
║ 1  │ A           ║
╚════╧═════════════╝

环境是SQL Server2012。您能提供帮助吗？

Answer 1

请尝试 - 您还应该检查您的设计???

解决方案

UPDATE a
SET a.ProductType = k.Pr
FROM 
HL_A a
INNER JOIN 
(
    SELECT u.ID , MAX(Pr) Pr
    FROM 
    (
        SELECT a.ID,STUFF((
                SELECT ', ' + CAST(c.Product AS VARCHAR(255))
                FROM HL_C c
                WHERE c.ID = b.ValueId
                FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)')
                ,1,2,'') Pr
        FROM HL_A a
        JOIN HL_B b
             ON a.ID = b.ID
    )u GROUP BY u.ID 
)k ON k.ID = a.ID
SELECT * FROM HL_A
GO

输出

ID          ProductType
----------- -----------
1           A, B, C
(1 row affected)

Answer 2

可以通过以下片段来实现更新 -

declare @a table(id int,producttype varchar(10))
declare @b table(id int,valueid int)
declare @c table(id int,product varchar(10))
insert into @a values(1,'')
insert into @b values(1,1),(1,2),(1,3)
insert into @c values(1,'A'),(2,'B'),(3,'C')
Update a
SET a.ProductType =
STUFF((
        SELECT ', ' + CAST(c.product AS VARCHAR(255))
        FROM @c c inner join
        @b b on c.ID = b.ValueId
        FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,2,'')
from @a a 
inner join @b b1
on a.id = b1.id
select * from @a

Answer 3

感谢其他人回答，但它们都没有起作用。最后，我实现了这样的目标：

Update a
SET a.ProductType = STUFF((
        SELECT ', ' + CAST(c.Product AS VARCHAR(255))
        FROM HL_C c
        WHERE c.ID IN (SELECT ValueID FROM HL_B WHERE ID = b.ID)
        FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,2,'')
FROM HL_A a
JOIN HL_B b
     ON a.ID = b.ID;