在同一 SELECT 语句中对多个列重复使用 Case 语句

Question

我有一个来自调查的大表，其中所有答案都是相似的，但我需要使它们保持一致。我需要对所有列重复使用相同的 case 语句，但这会导致非常难看且很长的查询。有没有办法将案例放入存储过程、UDF 或其他我可以更改被调用列的东西？

示例：

-- Example Case statement I need for all Columns
    CASE 
            WHEN Happy IN ('Yes','True','Y','1') THEN 'Yes'
            WHEN Happy IN ('No','False','N','0') THEN 'No'
            WHEN Happy LIKE 'Don_t Know' or Happy IN ('Unknown','U','999','-1') THEN 'Unknown'
            WHEN Happy IN ('Missing','Blank','-4') THEN 'Missing'
    END AS HappyClean

-- Example table
SELECT Happy
      ,Sad
      ,DownBad
      ,FeelinGood
From Emotions

实际的列数超过 50，而 Case 实际上长了 2 行，因此您可以看到如果我必须为每列复制粘贴该代码，代码会有多长。顺便说一句，我正在使用 SQL Server。

感谢任何帮助！

Answer 1

您可以创建一个映射表，插入所有替换对一次，然后连接：

CREATE TABLE #Mapping
(
  Candidate varchar(255),
  Replacement varchar(255)
);

INSERT #Mapping(Candidate, Replacement)
VALUES('Yes','Yes'),('True','Yes'),('Y','Yes'),('1','Yes'),
  ('No','No'),('False','No'),('N','No'),('0','No'),
  ('Don_t Know','Unknown'),('Unknown','Unknown'),
  ('U','Unknown'),('999','Unknown'),('-1','Unknown'),
  ('Missing','Missing'),('Blank','Missing'),('-4','Missing');

UPDATE e SET Happy = m.Replacement
  FROM dbo.Emotions AS e
  INNER JOIN #Mapping AS m
  ON e.Happy LIKE m.Candidate;

Answer 2

您还可以使用虚拟 VALUES 表

SELECT
  HappyClean = m.Replacement
FROM dbo.Emotions AS e
INNER JOIN (VALUES
  ('Yes','Yes'),('True','Yes'),('Y','Yes'),('1','Yes'),
  ('No','No'),('False','No'),('N','No'),('0','No'),
  ('Don_t Know','Unknown'),('Unknown','Unknown'),
  ('U','Unknown'),('999','Unknown'),('-1','Unknown'),
  ('Missing','Missing'),('Blank','Missing'),('-4','Missing')
) AS m(Candidate, Replacement) ON e.Happy LIKE m.Candidate;

Answer 3

我可能会创建一个映射表，并在查询中使用左联接链接到它，以获取那些仍然与任何内容都不匹配的答案。

CREATE TABLE MAPPING (a nvarchar(100), b nvarchar(100))

INSERT INTO MAPPING
VALUES  ('Yes','Yes'),('True','Yes'),('Y','Yes'),('1','Yes'),
        ('No','No'),('False','No'),('N','No'),('0','No'),
        ('Don_t Know','Unknown'),('Unknown','Unknown'),
        ('U','Unknown'),('999','Unknown'),('-1','Unknown'),
        ('Missing','Missing'),('Blank','Missing'),('-4','Missing');

SELECT Happy
      ,m1.b as HappyResult
      ,Sad
      ,m2.b as SadResult
      ,DownBad
      ,m3.b as DownbadResult
      ,FeelinGood
      ,m4.b as FeelinGoodResult
From Emotions e
left join MAPPING m1 on m1.a = e.Happy
left join MAPPING m2 on m2.a = e.Sad
left join MAPPING m3 on m3.a = e.DownBad
left join MAPPING m4 on m4.a = e.FeelinGood