How to merge three columns values uniquely using UNION ALL in SQL Server

Hi nedash,

You need to use UNION ALL by taking three select statements in each statement you have to mention separate column name from which column you have to get values. Also you need to check if it not null or string not empty in where clause. On each select you also have make sure to type DISTINCT before the column name. And assign the meaningful column name to it so you can get output with related column name.

hi kalpesh

can you give me example?

Best regards

neda

hi nedash,

Refer the below test query for tour reference.

DECLARE @Film AS TABLE(Id INT,Genre1 VARCHAR(10),Genre2 VARCHAR(10),Genre3 VARCHAR(10))

INSERT INTO @Film
SELECT 1,'Drame',' ','Action'
UNION ALL
SELECT 2,'','Family','Action'

SELECT DISTINCT Genre1 as Genre
FROM @Film
WHERE LTRIM(RTRIM(ISNULL(Genre1,''))) <> ''
UNION ALL
SELECT DISTINCT Genre2 as Genre
FROM @Film
WHERE LTRIM(RTRIM(ISNULL(Genre2,''))) <> ''
UNION ALL
SELECT DISTINCT Genre3 as Genre
FROM @Film
WHERE LTRIM(RTRIM(ISNULL(Genre3,''))) <> ''

OUTPUT