如果我有一个值，请从内容中删除逗号

Question

我想在末尾删除逗号，如果东西内部只有一个值，但是它应该存在多个值谢谢！

select
Names= (STUFF((
  SELECT ', ' +     CONCAT(
                CASE WHEN RegistryUser.FirstName IS NOT NULL AND RegistryUser.LastName IS NOT NULL THEN RegistryUser.FirstName ELSE App.FirstName END,
                ' ',
                CASE WHEN RegistryUser.FirstName IS NOT NULL AND RegistryUser.LastName IS NOT NULL THEN RegistryUser.LastName ELSE App.LastName END
            ) AS Names
    FROM ConsultingEventConsultant
            LEFT JOIN App
                ON ConsultingEventConsultant.AppID = App.AppID
            LEFT JOIN RegistryUser
                ON App.UserID = RegistryUser.UserID
            WHERE ConsultingEventConsultant.consultingeventid = 56428 
            and ConsultingEventConsultant.CECID != (SELECT TOP 1 CECID FROM ConsultingEventConsultant CEC WHERE CEC.ConsultingEventID = 56428 ORDER BY CECID)
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
             ), 1, 1, ''))

John Doe

约翰·杜(John Doe(，萨拉·史密斯(Sara Smith(

Answer 1

正如约翰·卡佩莱蒂(John Cappelleti(所说，您正在删除领先的逗号(不是领先的空间(，如果有一个落后逗号，则您将返回空白行。

要删除领先空间，只需将第二个1在末端附近更改为2如下。

。

select
Names= (STUFF((
  SELECT ', ' +     CONCAT(
                CASE WHEN RegistryUser.FirstName IS NOT NULL AND RegistryUser.LastName IS NOT NULL THEN RegistryUser.FirstName ELSE App.FirstName END,
                ' ',
                CASE WHEN RegistryUser.FirstName IS NOT NULL AND RegistryUser.LastName IS NOT NULL THEN RegistryUser.LastName ELSE App.LastName END
            ) AS Names
    FROM ConsultingEventConsultant
            LEFT JOIN App
                ON ConsultingEventConsultant.AppID = App.AppID
            LEFT JOIN RegistryUser
                ON App.UserID = RegistryUser.UserID
            WHERE ConsultingEventConsultant.consultingeventid = 56428 
            and ConsultingEventConsultant.CECID != (SELECT TOP 1 CECID FROM ConsultingEventConsultant CEC WHERE CEC.ConsultingEventID = 56428 ORDER BY CECID)
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
             ), 1, 2, ''))

查看有关空白行的更多信息，请运行以下内容。然后，您应该能够找到错误的数据并修复它或查询中的错误逻辑(以空白为准(。

SELECT ', ' +     CONCAT(
                CASE WHEN RegistryUser.FirstName IS NOT NULL AND RegistryUser.LastName IS NOT NULL THEN RegistryUser.FirstName ELSE App.FirstName END,
                ' ',
                CASE WHEN RegistryUser.FirstName IS NOT NULL AND RegistryUser.LastName IS NOT NULL THEN RegistryUser.LastName ELSE App.LastName END
            ) AS Names,
            *
    FROM ConsultingEventConsultant
            LEFT JOIN App
                ON ConsultingEventConsultant.AppID = App.AppID
            LEFT JOIN RegistryUser
                ON App.UserID = RegistryUser.UserID
            WHERE ConsultingEventConsultant.consultingeventid = 56428 
            and ConsultingEventConsultant.CECID != (SELECT TOP 1 CECID FROM ConsultingEventConsultant CEC WHERE CEC.ConsultingEventID = 56428 ORDER BY CECID)

Answer 2

我建议在找不到名称时注意。也许这做了您需要的事情：

select STUFF((SELECT ', ' + 
                     COALESCE(ru.FirstName + ' ' + ru.LastName, 
                              a.FirstName + ' ' + a.LastName,
                              '<NO NAME FOUND>'
                             ) AS Names
              FROM ConsultingEventConsultant c3c LEFT JOIN
                   App a
                   ON cec.AppID = a.AppID LEFT JOIN
                   RegistryUser ru
                   ON a.UserID = ru.UserID
              WHERE cec.consultingeventid = 56428 AND
                    cec.CECID <> (SELECT TOP 1 cec2CECID
                                  FROM ConsultingEventConsultant CEC2 
                                  WHERE CEC2.ConsultingEventID = 56428                                 ORDER BY CECID
                                 )
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
             ), 1, 2, ''
            ) as names

另外，您可能需要添加这样的逻辑：

 (ru.FirstName IS NOT NULL OR a.FirstName IS NOT NULL) AND

到WHERE子句。