sql server 查询分组后用逗号拼接字符串和拆分

1、创建表

CREATE TABLE [dbo].[m_test] (
[A] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[B] varchar(max) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[m_test] SET (LOCK_ESCALATION = TABLE);

 

2、插入数据

INSERT INTO [dbo].[m_test] ([A], [B]) VALUES (N'A', N'A001')
GO

INSERT INTO [dbo].[m_test] ([A], [B]) VALUES (N'A', N'A002')
GO

INSERT INTO [dbo].[m_test] ([A], [B]) VALUES (N'A', N'A003')
GO

INSERT INTO [dbo].[m_test] ([A], [B]) VALUES (N'B', N'B001')
GO

INSERT INTO [dbo].[m_test] ([A], [B]) VALUES (N'B', N'B002')
GO

 

3、例子

 

 

select
a,
b=stuff((select ','+b from m_test where a=t.a for xml path('')),1,1,'')
from
m_test t

 

 

 

select
a,
b=stuff((select ','+b from m_test where a=t.a for xml path('')),1,1,'')
from
m_test t
group by
a;