欢迎光临
个人知识库,ERP、IT知识分享和应用

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

未经允许不得转载:Blog.XiaoMing.Xyz » SqlServer-STUFF拼接列表
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址