for path多行变一列
--创建表
if exists (select * from sysobjects where id = OBJECT_ID('[TempTable_Base]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [TempTable_Base]
CREATE TABLE [TempTable_Base] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[guid] [varchar] (50) NULL,
[code] [varchar] (50) NULL)
SET IDENTITY_INSERT [TempTable_Base] ON
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 1,'91E92DCB-141A-30B2-E6CD-B59EABD21749','A')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 2,'91E92DCB-141A-30B2-E6CD-B59EABD21749','C')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 3,'91E92DCB-141A-30B2-E6CD-B59EABD21749','E')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 4,'91E92DCB-141A-30B2-E6CD-B59EABD21749','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 5,'91E92DCB-141A-30B2-E6CD-B59EABD21749','G')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 6,'79DD7AB9-CE57-9431-B020-DF99731FC99D','A')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 7,'79DD7AB9-CE57-9431-B020-DF99731FC99D','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 8,'79DD7AB9-CE57-9431-B020-DF99731FC99D','E')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 9,'79DD7AB9-CE57-9431-B020-DF99731FC99D','F')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 10,'79DD7AB9-CE57-9431-B020-DF99731FC99D','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 11,'79DD7AB9-CE57-9431-B020-DF99731FC99D','B')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 12,'79DD7AB9-CE57-9431-B020-DF99731FC99D','D')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 13,'79DD7AB9-CE57-9431-B020-DF99731FC99D','F')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 14,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 15,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','D')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 16,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','F')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 17,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','C')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 18,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','U')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 19,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','F')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 20,'4802F0CD-B53F-A3F5-1C78-2D7424579C06','A')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 21,'3CCBFF9F-827B-6639-4780-DA7215166728','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 22,'3CCBFF9F-827B-6639-4780-DA7215166728','M')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 23,'3CCBFF9F-827B-6639-4780-DA7215166728','C')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 24,'3CCBFF9F-827B-6639-4780-DA7215166728','M')
SET IDENTITY_INSERT [TempTable_Base] OFF
SELECT B.guid,LEFT(UserList,LEN(UserList)-1) as paths FROM (
SELECT guid,
(SELECT code+',' FROM TempTable_Base WHERE guid=A.guid ORDER BY ID FOR XML PATH('')) AS UserList
FROM TempTable_Base A
GROUP BY guid
) B
if exists (select * from sysobjects where id = OBJECT_ID('[TempTable_Base]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [TempTable_Base]
CREATE TABLE [TempTable_Base] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[guid] [varchar] (50) NULL,
[code] [varchar] (50) NULL)
SET IDENTITY_INSERT [TempTable_Base] ON
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 1,'91E92DCB-141A-30B2-E6CD-B59EABD21749','A')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 2,'91E92DCB-141A-30B2-E6CD-B59EABD21749','C')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 3,'91E92DCB-141A-30B2-E6CD-B59EABD21749','E')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 4,'91E92DCB-141A-30B2-E6CD-B59EABD21749','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 5,'91E92DCB-141A-30B2-E6CD-B59EABD21749','G')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 6,'79DD7AB9-CE57-9431-B020-DF99731FC99D','A')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 7,'79DD7AB9-CE57-9431-B020-DF99731FC99D','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 8,'79DD7AB9-CE57-9431-B020-DF99731FC99D','E')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 9,'79DD7AB9-CE57-9431-B020-DF99731FC99D','F')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 10,'79DD7AB9-CE57-9431-B020-DF99731FC99D','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 11,'79DD7AB9-CE57-9431-B020-DF99731FC99D','B')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 12,'79DD7AB9-CE57-9431-B020-DF99731FC99D','D')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 13,'79DD7AB9-CE57-9431-B020-DF99731FC99D','F')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 14,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 15,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','D')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 16,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','F')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 17,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','C')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 18,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','U')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 19,'D61651D9-1B0A-0362-EE91-A805AA3E08F2','F')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 20,'4802F0CD-B53F-A3F5-1C78-2D7424579C06','A')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 21,'3CCBFF9F-827B-6639-4780-DA7215166728','O')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 22,'3CCBFF9F-827B-6639-4780-DA7215166728','M')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 23,'3CCBFF9F-827B-6639-4780-DA7215166728','C')
INSERT [TempTable_Base] ([id],[guid],[code]) VALUES ( 24,'3CCBFF9F-827B-6639-4780-DA7215166728','M')
SET IDENTITY_INSERT [TempTable_Base] OFF
SELECT B.guid,LEFT(UserList,LEN(UserList)-1) as paths FROM (
SELECT guid,
(SELECT code+',' FROM TempTable_Base WHERE guid=A.guid ORDER BY ID FOR XML PATH('')) AS UserList
FROM TempTable_Base A
GROUP BY guid
) B
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 周边上新:园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源!
· .NET周刊【3月第1期 2025-03-02】
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器