SQL Server中的Stuff函数 FOR XML PATH 合并多行数据成一行, 显示部门、省市 层级关系

STUFF ( character_expression , start , length ,character_expression ) 

start是从1开始

示例 
以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串。

SELECT STUFF('abcdef', 2, 3, 'ijklmn'); 

下面是结果集： 

aijklmnef

 

 

SELECT DISTINCT
       StoreID,
       STUFF(
       (
           SELECT ',' + UserName
           FROM Accounts_Users_CMS
           WHERE StoreID = A.StoreID
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) AS UserNames
FROM
(
    SELECT StoreID,
           UserName
    FROM Accounts_Users_CMS
    GROUP BY StoreID,
             UserName
) A;

 

Sqlserver如何递归查询层级数据将父级字段和本级某个字段合并？

------查询树结构某节点的上级所有跟节点

WITH areadata (sID, sSuperID, sName)
AS (

---起始条件
SELECT m.sID,
m.sSuperID,
m.sName
FROM [AdoptionRegister_Membership].[dbo].[mdb_Area] m
WHERE sID = '340824' --列出子节点查询条件

--递归条件
UNION ALL
SELECT a.sID,
a.sSuperID,
a.sName
FROM [AdoptionRegister_Membership].[dbo].[mdb_Area] a
INNER JOIN areadata b
ON a.sID = b.sSuperID

--根据子节点父级字段查询父级信息
)
SELECT *
FROM areadata;

 

 

 

select stuff((
select ''+sName
from areadata where sID!='000000' order by sID asc for xml path('')),1,0,'') as name ; -----sID!='000000' 这里是过滤全国这行数据

 

WITH areadata (DeptId, ParentId, DeptName, PathName)
AS (SELECT dept.DeptId,
           dept.Upperid AS ParentId,
           dept.DeptName,
           convert(nvarchar(max), dept.DeptName) as PathName
    FROM CRM_DeptInfo dept
    WHERE Upperid != -1
    UNION ALL
    SELECT deptinfo.DeptId,
                        b.ParentId AS ParentId,          
           deptinfo.DeptName,
           convert(nvarchar(max), b.PathName + '/' + deptinfo.DeptName) as PathName
    FROM CRM_DeptInfo deptinfo
        INNER JOIN areadata b
            ON deptinfo.Upperid = b.DeptId)
            
       select * from areadata  WHERE areadata.DeptId=57

 

 

 

SELECT b.*,
                                       c.StoreName AS ClientName, au.UserName,
                                       td.Product,
                                        (
                                           SELECT SUM(TotalNum)
                                           FROM CRM_StoreInBillDetail
                                           WHERE InBillNo = b.InBillNo
                                         ) countnums,
                                       ROW_NUMBER() OVER (ORDER BY b.InBillNo DESC) rows
                                FROM CRM_StoreInBillList b
                                    LEFT JOIN dbo.T_Store c
                                        ON b.OriginalClientCode = CONVERT(NVARCHAR(50), c.StoreId)
                                    LEFT JOIN dbo.Accounts_Users_CMS au
                                        ON au.StoreID = c.StoreId
                                    LEFT JOIN
                                    (
                                        SELECT DISTINCT
                                               InBillNo,
                                               STUFF(
                                               (
                                                   SELECT '；' + Product
                                                   FROM
                                                   (
                                                       SELECT InBillNo,
                                                              ProductName + '（数量：' + CONVERT(VARCHAR, ISNULL(SUM(TotalNum), 0)) + '）' AS Product
                                                       FROM CRM_StoreInBillDetail
                                                       GROUP BY InBillNo,
                                                                ProductCode,
                                                                ProductName
                                                   ) d
                                                   WHERE InBillNo = T.InBillNo
                                                   FOR XML PATH('')
                                               ),
                                               1,
                                               1,
                                               ''
                                                    ) AS Product
                                        FROM
                                        (
                                            SELECT InBillNo,
                                                   ProductName + '（数量：' + CONVERT(VARCHAR, ISNULL(SUM(TotalNum), 0)) + '）' AS Product
                                            FROM CRM_StoreInBillDetail
                                            GROUP BY InBillNo,
                                                     ProductCode,
                                                     ProductName
                                        ) AS T
                                    ) td
                                        ON td.InBillNo = b.InBillNo
                                WHERE 1 = 1 

 

 

 

参考：

https://www.cnblogs.com/wrzszhaowei/p/4682798.html

 

https://www.cnblogs.com/zuozongyao/p/8258737.html

 

https://blog.csdn.net/gl794262724/article/details/82020470