Checking multiple columns for single value and returning only the single value-CodePudding

I want to search for a single value in 3 columns and return each occurrence of this value. My query is based on the following answer regarding a similar request.

SELECT * 
FROM table 
WHERE 'abc' IN (column1, column2, column3)

However, I don't want to return the whole row, only the single value. It possible that the value can found in multiple columns for the same row. Each occurrence should be returned and the end result should be a 1-dimensional list. How do I need to alter my query?

CodePudding user response：

The value that you want returned in each row is the same as the value that you search for, so a statement like this will do:

SELECT 'abc' AS column_name 
FROM table 
WHERE 'abc' IN (column1, column2, column3)

If you want 'abc' returned once for each occurrence in any of the 3 columns you should use UNION ALL:

SELECT column1 AS column_name 
FROM table 
WHERE column1 = 'abc'
UNION ALL
SELECT column2 AS column_name 
FROM table 
WHERE column2 = 'abc'
UNION ALL
SELECT column3 AS column_name 
FROM table 
WHERE column3 = 'abc'