how can i get my case statement to return both prefer action and prefer comedy in my custom preferen-CodePudding

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

CodePudding user response：

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