从Case Statement中删除逗号

问题描述：

我意识到这是一个常见问题，但我想避免重复CASE语句多次。从Case Statement中删除逗号

任何人都可以看到如何从字符串中排除“，”如果只有几个案例是真实的（1）？

SELECT CONCAT 
(
    CASE WHEN Home = 1 THEN 'Own Home, ' END, 
    CASE WHEN Car = 1 THEN 'Car, ' END, 
    CASE WHEN Friend = 1 THEN 'Someone else''s home, ' END, 
    CASE WHEN Toilet = 1 THEN 'Toilet, ' END, 
    CASE WHEN Street = 1 THEN 'Street, park or beach, ' END, 
    CASE WHEN Squat = 1 THEN 'Squat, ' END, 
    CASE WHEN Other = 1 THEN 'Other' END 
) 
as 'Question 6', 
from table

答

试试这个

SELECT REVERSE(stuff(reverse(CONCAT 
(
    CASE WHEN Home = 1 THEN 'Own Home, ' END, 
    CASE WHEN Car = 1 THEN 'Car, ' END, 
    CASE WHEN Friend = 1 THEN 'Someone else''s home, ' END, 
    CASE WHEN Toilet = 1 THEN 'Toilet, ' END, 
    CASE WHEN Street = 1 THEN 'Street, park or beach, ' END, 
    CASE WHEN Squat = 1 THEN 'Squat, ' END, 
    CASE WHEN Other = 1 THEN 'Other' END 
)),1 ,1,'')) 
as 'Question 6', 
from TABLE

或本

SELECT STUFF(CONCAT 
(
    CASE WHEN Home = 1 THEN ', Own Home' END, 
    CASE WHEN Car = 1 THEN ', Car' END, 
    CASE WHEN Friend = 1 THEN ', Someone else''s home' END, 
    CASE WHEN Toilet = 1 THEN ', Toilet' END, 
    CASE WHEN Street = 1 THEN ', Street, park or beach' END, 
    CASE WHEN Squat = 1 THEN ', Squat' END, 
    CASE WHEN Other = 1 THEN ', Other' END 
),1,2,'') 
as 'Question 6', 
from table

谢谢，但都不幸工作。 – Philip

@詹姆斯你面临的问题是什么？ –

我试着用示例数据，它工作正常。你能提供你的样本数据和预期的输出。 –

答

它可能不是最快的，但只是为了好玩。

WITH T3 AS 
(
    SELECT T1.PK, COALESCE(T2.Description, unpvt.Place) AS Description 
    FROM table AS T1 
    UNPIVOT 
     (Flag FOR Place IN (Home, Car, Friend, Toilet, Street, Squat, Other) 
    ) AS unpvt 
    LEFT OUTER JOIN 
    (
     SELECT * 
     FROM (VALUES('Home', 'Own Home'), ('Friend', 'Someone else''s home'), ('Street', 'Street, park or beach')) AS T(Place, Description) 
    ) AS T2 ON unpvt.Place = T2.Place 
    WHERE Flag = 1 
) 
SELECT PK, (STUFF((
       SELECT ', ' + Description 
       FROM T3 AS T4 
       WHERE T4.PK = T3.PK 
       FOR XML PATH('')), 1, 2, '')) AS Question6 
FROM T3 
GROUP BY PK

要保留有问题的订单，只需在上面的T2中添加一个订单栏即可。

对于SQL Server 2017，它更简单。

SELECT T1.PK, STRING_AGG(COALESCE(T2.Description, unpvt.Place), ', ') AS Description 
FROM table AS T1 
UNPIVOT 
    (Flag FOR Place IN (Home, Car, Friend, Toilet, Street, Squat, Other) 
) AS unpvt 
LEFT OUTER JOIN 
(
    SELECT * 
    FROM (VALUES('Home', 'Own Home'), ('Friend', 'Someone else''s home'), ('Street', 'Street, park or beach')) AS T(Place, Description) 
) AS T2 ON unpvt.Place = T2.Place 
WHERE Flag = 1 
GROUP BY PK

从Case Statement中删除逗号

相关推荐