'how can i get my case statement to return both prefer action and prefer comedy in my custom preference column if a person has both marked as 1

SELECT FirstName, LastName,

CASE 

WHEN [Action] = '1' THEN 'Prefer Action'

WHEN Comedy = '1' THEN 'Prefer Comedy'

WHEN Drama = '1' THEN 'Prefer Drama'

WHEN Horror = '1' THEN 'Prefer Horror'

WHEN Romance = '1' THEN 'Prefer Romance'
ELSE ' '
END AS 'Customer Preference'

FROM RentalMDB.dbo.Customers
mysqlsqlcase


Solution 1:[1]

You can use below Union statement,

SELECT FirstName, LastName,
CASE 
WHEN [Action] = '1' THEN 'Prefer Action'
WHEN Drama = '1' THEN 'Prefer Drama'
WHEN Horror = '1' THEN 'Prefer Horror'
WHEN Romance = '1' THEN 'Prefer Romance'
ELSE ' '
END AS 'Customer Preference'
FROM RentalMDB.dbo.Customers

UNION ALL

SELECT FirstName, LastName,
CASE 
WHEN Comedy = '1' THEN 'Prefer Comedy'
WHEN Drama = '1' THEN 'Prefer Drama'
WHEN Horror = '1' THEN 'Prefer Horror'
WHEN Romance = '1' THEN 'Prefer Romance'
ELSE ' '
END AS 'Customer Preference'
FROM RentalMDB.dbo.Customers

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jim Macaulay

Related Questions