SQLServer 类似MySQL的group_concat()，for xml path

后山吹大风

已于 2022-11-17 16:23:42 修改

阅读量693

点赞数

分类专栏： mssql 文章标签： mysql sqlserver 数据库

于 2022-11-17 16:21:31 首次发布

本文链接：https://blog.csdn.net/qq_31834115/article/details/127905822

版权

mssql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

查询结果截图，如下：

1.建表语句

CREATE TABLE [dbo].[A](
	[TERMCODE] [nvarchar](255) NULL,
	[CNOMEN] [nvarchar](255) NULL,
	[ENOMEN] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00000', N'0章 皮肤和皮下组织疾病', N'CHAPTER 0 DISEASES OF THE SKIN AND SUBCUTANEOUS TISSUES')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00000', N'0-0节 皮肤和皮下组织疾病:一般术语、组织学类型和感染', N'SECTION 0-0 DISEASE OF THE SKIN AND SUBCUTANEOUS TISSUES: GENERAL TERMS, HISTOLOGIC TYPES AND INFECTIONS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00000', N'皮肤和皮下组织疾病:一般术语和组织学类型', N'0-00 DISEASES OF THE SKIN AND SUBCUTANEOUS TISSUES: GENERAL TERMS AND HISTOLOGIC TYPES')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00000', N'皮肤和皮下组织疾病:一般术语', N'0-000 DISEASES OF THE SKIN AND SUBCUTANEOUS TISSUES: GENERAL TERMS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00000', N'皮肤和皮下组织疾病, NOS', N'Disease of skin and subcutaneous tissue, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00004', N'皮肤疾病, NOS', N'Disease of skin, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00004', N'皮肤病症, NOS', N'Skin disorder, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00004', N'皮肤病, NOS', N'Dermatosis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00010', N'皮炎, NOS', N'Dermatitis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00010', N'皮肤炎症, NOS', N'Inflammation of skin, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00010', N'皮肤刺激, NOS', N'Skin irritation, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00011', N'肢皮炎, NOS', N'Acrodermatitis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00012', N'急性皮炎, NOS', N'Acute dermatitis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00013', N'急性小泡性皮炎, NOS', N'Acute vesicular dermatitis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00013', N'急性大疱性皮炎, NOS', N'Acute bullous dermatitis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00014', N'亚急性皮炎, NOS', N'Subacute dermatitis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00014', N'急性和慢性皮炎', N'Acute and chronic dermatitis')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00016', N'慢性皮炎, NOS', N'Chronic dermatitis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00018', N'新生儿皮炎, NOS', N'Dermatitis of the newborn, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00018', N'新生儿皮炎, NOS', N'Neonatal dermatitis, NOS')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00020', N'皮肤硬结', N'Induration of skin')
GO
INSERT [dbo].[A] ([TERMCODE], [CNOMEN], [ENOMEN]) VALUES (N'D0-00026', N'皮肤增厚', N'Thickening of skin')
GO

2.执行字段拼接查询

SELECT [TERMCODE]   ,
stuff((SELECT '-' + [CNOMEN] FROM A WHERE [TERMCODE] = t.[TERMCODE]FOR xml path('') ),1, 1, '' )AS [CNOMEN] ,
stuff((SELECT '-' + [ENOMEN] FROM A WHERE [TERMCODE] = t.[TERMCODE]FOR xml path('') ),1, 1, '' )AS [ENOMEN]
from A AS t   GROUP BY  [TERMCODE]