Reusing Case Statement for many Columns in same SELECT statement-CodePudding

I have a big table from a survey where all the answers are similar but I need to make them consistent. I need to reuse the same case statement for all the columns, however that would lead to a very ugly and long query. Is there a way to make a case into a stored procedure, UDF or something where I can just change the called column?

Examples:

-- 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

The actual number of Columns is over 50 and the Case is actually 2 lines longer so you can see how long the code would be if I had to copy paste that code for every column. I am using SQL Server btw.

Appreciate any help!

CodePudding user response：

You can just create a mapping table, insert all the replacement pairs once, then join:

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;

CodePudding user response：

You can also use a virtual VALUES table

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;

CodePudding user response：

I might create a mapping table and link to it in the query using left joins for those answers that still don't match with anything.

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