SQL Sever中多列拼接成一列值为NULL

查询出数据

SELECT    a.ID AS KYMain_ID ,
                    ',' + a.Leader + ',' AS KYMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_KYChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.Single_Leader)
                            FROM    TB_KYSubProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYSub_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.KY_KCUser)
                            FROM    TB_KYSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYTask_Leader ,
                    ',' + b.Leader + ',' AS SJMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_SJChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SJ_User)
                            FROM    TB_SJSubProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJSub_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SJ_KCUser)
                            FROM    TB_SJSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJTask_Leader ,
                    ',' + c.Leader + ',' AS SGMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_SGChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SG_U_User)
                            FROM    TB_SGSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGTask_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SG_KCUser)
                            FROM    TB_SGSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGTask_KCLeader
          FROM      TB_KYMainProject a
                    LEFT JOIN TB_SJMainProject b ON b.ID = a.ID
                    LEFT JOIN TB_SGMainProject c ON c.ID = a.ID
          WHERE     ISNULL(a.IsDelete, 0) = 0
                    AND ISNULL(b.IsDelete, 0) = 0
                    AND ISNULL(c.IsDelete, 0) = 0

然后业务需要，把多列拼接成一列，但显示NULL

SELECT  [KYMain_Leader] + [KYChild_Leader] + [KYSub_Leader] + [KYTask_Leader]+ [SJMain_Leader] + [SJChild_Leader] + [SJSub_Leader]+ [SJTask_Leader] + [SGMain_Leader] + [SGChild_Leader]+ [SGTask_Leader] + [SGTask_KCLeader]
FROM    ( SELECT    a.ID AS KYMain_ID ,
                    ',' + a.Leader + ',' AS KYMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_KYChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.Single_Leader)
                            FROM    TB_KYSubProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYSub_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.KY_KCUser)
                            FROM    TB_KYSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYTask_Leader ,
                    ',' + b.Leader + ',' AS SJMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_SJChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SJ_User)
                            FROM    TB_SJSubProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJSub_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SJ_KCUser)
                            FROM    TB_SJSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJTask_Leader ,
                    ',' + c.Leader + ',' AS SGMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_SGChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SG_U_User)
                            FROM    TB_SGSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGTask_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SG_KCUser)
                            FROM    TB_SGSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGTask_KCLeader
          FROM      TB_KYMainProject a
                    LEFT JOIN TB_SJMainProject b ON b.ID = a.ID
                    LEFT JOIN TB_SGMainProject c ON c.ID = a.ID
          WHERE     ISNULL(a.IsDelete, 0) = 0
                    AND ISNULL(b.IsDelete, 0) = 0
                    AND ISNULL(c.IsDelete, 0) = 0
        ) vv

解决方法：

null加减乘除任何值都等于null，所以使用isnull函数先处理下列的值再拼接

SELECT  ISNULL([KYMain_Leader], '') + ISNULL([KYChild_Leader], '')
        + ISNULL([KYSub_Leader], '') + ISNULL([KYTask_Leader], '')
        + ISNULL([SJMain_Leader], '') + ISNULL([SJChild_Leader], '')
        + ISNULL([SJSub_Leader], '') + ISNULL([SJTask_Leader], '')
        + ISNULL([SGMain_Leader], '') + ISNULL([SGChild_Leader], '')
        + ISNULL([SGTask_Leader], '') + ISNULL([SGTask_KCLeader], '')
FROM    ( SELECT    a.ID AS KYMain_ID ,
                    ',' + a.Leader + ',' AS KYMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_KYChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.Single_Leader)
                            FROM    TB_KYSubProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYSub_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.KY_KCUser)
                            FROM    TB_KYSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS KYTask_Leader ,
                    ',' + b.Leader + ',' AS SJMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_SJChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SJ_User)
                            FROM    TB_SJSubProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJSub_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SJ_KCUser)
                            FROM    TB_SJSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SJTask_Leader ,
                    ',' + c.Leader + ',' AS SGMain_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.TaskLeader)
                            FROM    TB_SGChildProject b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGChild_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SG_U_User)
                            FROM    TB_SGSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGTask_Leader ,
                    STUFF(( SELECT  ',' + CONVERT(VARCHAR(36), b.SG_KCUser)
                            FROM    TB_SGSubTask b
                            WHERE   b.MainProjectID = a.ID
                                    AND ISNULL(b.IsDelete, 0) = 0
                          FOR
                            XML PATH('')
                          ), 1, 0, '') + ',' AS SGTask_KCLeader
          FROM      TB_KYMainProject a
                    LEFT JOIN TB_SJMainProject b ON b.ID = a.ID
                    LEFT JOIN TB_SGMainProject c ON c.ID = a.ID
          WHERE     ISNULL(a.IsDelete, 0) = 0
                    AND ISNULL(b.IsDelete, 0) = 0
                    AND ISNULL(c.IsDelete, 0) = 0
        ) vv

学习，以记之。如有错漏，欢迎指正

作者：冯子武
出处：http://www.cnblogs.com/Zev_Fung/
本文版权归作者和博客园所有，欢迎转载，转载请标明出处。
如果博文对您有所收获，请点击下方的 [推荐]，谢谢

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Zev_Fung/p/8145250.html