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