Select distinct from multiple fields using sql

This should give you all distinct values from the table. I presume you'd want to add where clauses to select only for a particular question. However, this solution requires 5 subqueries and can be slow if your table is huge.

SELECT DISTINCT(ans) FROM (
    SELECT right AS ans FROM answers
    UNION
    SELECT wrong1 AS ans FROM answers
    UNION
    SELECT wrong2 AS ans FROM answers
    UNION
    SELECT wrong3 AS ans FROM answers
    UNION
    SELECT wrong4 AS ans FROM answers
) AS Temp

SELECT DISTINCT(ans) FROM (
    SELECT right AS ans FROM answers
    UNION
    SELECT wrong1 AS ans FROM answers
    UNION
    SELECT wrong2 AS ans FROM answers
    UNION
    SELECT wrong3 AS ans FROM answers
    UNION
    SELECT wrong4 AS ans FROM answers
) AS Temp

The DISTINCT is superfluous, because the UNION will not return rows that are identical for all columns. (When you want duplicated, or if you know that no duplicates exist, use UNION ALL for faster performance)

This will give you a single list containing all the answers. You'll still have duplicates though if you have multiple copies of the same answer within a single column.

That should not be the case if you use UNION, only if you use UNION ALL

SELECT [value] INTO #TEMP
FROM
(
SELECT  [value] = 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 1
) AS X

(4 row(s) affected)

SELECT [value] 
FROM    #TEMP

value       
----------- 
1
2
3
1

(4 row(s) affected)

SELECT [value] 
FROM    #TEMP
UNION 
SELECT [value]
FROM    #TEMP

value       
----------- 
1
2
3

(3 row(s) affected)

I provided one answer above.

However I figured a much better way to do it using UNPIVOT.

SELECT DISTINCT(ans)
FROM (
    SELECT [Name], ANS 
    FROM (
        SELECT right, wrong1, wrong2, wrong3, wrong4 
        FROM answers
    ) AS PVT
    UNPIVOT 
    (ans FOR [Name] IN (right, wrong1, wrong2, wrong3, wrong4)) AS UNPVT
) AS OUTPUT;

You can provide any WHERE clause in the internal subquery:

SELECT DISTINCT(ans)
FROM (
    SELECT [Name], ANS 
    FROM (
        SELECT right, wrong1, wrong2, wrong3, wrong4 
        FROM answers
        WHERE (...)
    ) AS PVT
    UNPIVOT 
    (ans FOR [Name] IN (right, wrong1, wrong2, wrong3, wrong4)) AS UNPVT
) AS OUTPUT;

Well you can use a UNION and run 5 select statements, one for each column in your table. It would look something like this:

SELECT right FROM answers
UNION
SELECT wrong1 FROM answers
UNION
SELECT wrong2 FROM answers
UNION
SELECT wrong3 FROM answers
UNION
SELECT wrong4 FROM answers

This will give you a single list containing all the answers. You'll still have duplicates though if you have multiple copies of the same answer within a single column.

Select distinct from multiple fields using sql

Related

Recent Posts