SqlServer-STUFF拼接列表

创建OrderInfo 表

CREATE TABLE OrderInfo
(
OrderGuid uniqueidentifier not null,
LoginId varchar(50) not null,
)

创建OrderProduct表

CREATE TABLE OrderProduct
(
ProductGuid uniqueidentifier not null,
ProductName varchar(100) not null,
OrderGuid uniqueidentifier not null,
)

向OrderInfo 表插入数据

insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),’15678975078′)
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),’15678975078′)
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),’15678971111′)
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),’15678971111′)
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),’15678971111′)
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),’15678971111′)
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),’15678971111′)
insert into OrderInfo(OrderGuid,LoginId)values(NEWID(),’15678973333′)

select * from OrderInfo

向OrderProduct表插入数据

insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’黑人牙膏’,’6646A662-6570-4A0D-B359-1B45D8441BA6′)
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’玻尿酸补水喷雾’,’6646A662-6570-4A0D-B359-1B45D8441BA6′)
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’白色帆布鞋’,’4B800C54-FDD8-496C-8414-C7122F708AA9′)
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’手镯’,’918DAD4C-71BA-4DBB-BD1D-09B5A75320F8′)
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’信纸’,’918DAD4C-71BA-4DBB-BD1D-09B5A75320F8′)
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’米老鼠’,’918DAD4C-71BA-4DBB-BD1D-09B5A75320F8′)
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’矿泉水’,’F8E1499F-6EEC-4AE6-8E77-0166D95116E1′)
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’眼镜’,’F8E1499F-6EEC-4AE6-8E77-0166D95116E1′)
insert into OrderProduct(ProductGuid,ProductName,OrderGuid)values(NEWID(),’机柜’,’4B800C54-FDD8-496C-8414-C7122F708AA9′)

select * from OrderProduct

同一张表数据的拼接

SELECT p2.LoginId, OrderGuid = STUFF((
SELECT ',' + convert(varchar(36),OrderGuid)
FROM OrderInfo AS p1
WHERE p1.LoginId = p2.LoginId
FOR XML PATH('')
), 1, 1, '') FROM OrderInfo AS p2
GROUP BY p2.LoginId

SELECT p2.OrderGuid, ProductName = STUFF((
SELECT ',' + ProductName
FROM OrderProduct AS p1
WHERE p1.OrderGuid = p2.OrderGuid
FOR XML PATH('')
), 1, 1, '') FROM OrderProduct AS p2
GROUP BY p2.OrderGuid

不同表数据的拼接

SELECT p2.OrderGuid,p2.LoginId, ProductName = STUFF((
SELECT ',' + ProductName
FROM OrderProduct AS p1
WHERE p1.OrderGuid = p2.OrderGuid
FOR XML PATH('')
), 1, 1, '') FROM OrderInfo AS p2
GROUP BY p2.OrderGuid,p2.LoginId

————————————————
版权声明：本文为CSDN博主「七海桑城」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_33556442/article/details/82149722