SQL Server 字符串合并

应用场景：多条数据某列数据以某种样式合并成一条数据，例如：

1    1,0    小赵    001
2    2,0    小钱    002                        =====》                         小赵(001),小钱(002),小李(004)
4    4,0    小李    004   

1、首先建立简化模型：假设老王有四个邻居，赵钱孙李，现在有四个小孩，小赵小钱小孙小李

                       

2、建表：

（1）建立小区住户表

CREATE TABLE xiaoqv(
    id INT NOT NULL,
    name NVARCHAR(50) NOT NULL
)
小区里有五位住户，插入小区住户表
INSERT INTO xiaoqv VALUES
(0,'隔壁老王'),
(1,'赵'),
(2,'钱'),
(3,'孙'),
(4,'李')

（2）建立班级学生表

CREATE TABLE class (
    id INT NOT NULL,
    parentid NVARCHAR(50) NOT NULL,
    name NVARCHAR(50) NOT NULL,
    phone NVARCHAR(50) NOT NULL
)

班里有4位学生，根据住户确认，插入班级学生表

INSERT INTO class VALUES
(1,'1,0','小赵','1001'),
(2,'2,0','小钱','1002'),
(3,'3','小孙','1003'),
(4,'4,0','小李','1004')

当前表中的数据为;

 

3.建立查询

SELECT x.name AS name,
(SELECT STUFF((SELECT ','+ e.info
    FROM (  
        SELECT ISNULL(name,'')+'('+ISNULL(xuehao,'*')+')' AS info FROM class c WHERE CHARINDEX(CONVERT(VARCHAR(50),x.id),c.parentid)>0
    )e FOR XML PATH(''))
    ,1,1,'')
)AS children
 FROM xiaoqv x

到这里就结束了

3.知识回顾

（1）字符串位置检索 charindex

 

（2）将表字段转化为xml表数据 for xml path('')

自定义分割格式

 

（3）字符串裁剪 stuff

看得出来，stuff(a,x,n,b)其意义为将字符串a从第x位开始，将其后的n位替换为 b

 当然这里用 substring也可以，只是需要获取未知字符串长度，需要获取两次，代码比较臃肿，效率还低

全部sql语句

CREATE TABLE xiaoqv(
    id INT NOT NULL,
    name NVARCHAR(50) NOT NULL
)

INSERT INTO xiaoqv VALUES
(0,'隔壁老王'),
(1,'赵'),
(2,'钱'),
(3,'孙'),
(4,'李')

CREATE TABLE class (
    id INT NOT NULL,
    parentid NVARCHAR(50) NOT NULL,
    name NVARCHAR(50) NOT NULL,
    xuehao NVARCHAR(50) NOT NULL
)
INSERT INTO class VALUES
(1,'1,0','小赵','1001'),
(2,'2,0','小钱','1002'),
(3,'3','小孙','1003'),
(4,'4,0','小李','1004')

SELECT * FROM dbo.xiaoqv
SELECT * FROM dbo.class

SELECT x.name AS name,
(SELECT STUFF((SELECT ','+ e.info 
    FROM (  
        SELECT ISNULL(name,'')+'('+ISNULL(xuehao,'*')+')' AS info FROM class c WHERE CHARINDEX(CONVERT(VARCHAR(50),x.id),c.parentid)>0
    )e FOR XML PATH(''))
    ,1,1,'')
)AS children
 FROM xiaoqv x




 SELECT CHARINDEX('3','123456789')

 SELECT name FROM class FOR XML PATH('')

 SELECT ','+name FROM class FOR XML PATH('')

 SELECT STUFF('123456789',2,5,'')

 SELECT SUBSTRING('123456789',2,LEN('123456789')-1)