Select only distinct values from two columns from a table

Question

If I have a table such as

1 A

1 B

1 A

1 B

2 C

2 C

And I want to select distinct from the two columns so that I would get

How can I word my query? Is the only way to concatenate the columns and wrap them around a distinct function operator?

Andomar · Accepted Answer · 2013-05-14T06:52:37.803

7

You could use a union to create a table of all values from both columns:

select  col1 as BothColumns
from    YourTable
union
select  col2
from    YourTable

Unlike union all, union removes duplicates, even if they come from the same side of the union.

edited May 14 '13 at 06:52

answered May 14 '13 at 06:12

Andomar

232,371
49
380
404

1

@Luv: The outer `distinct` will take care of removing duplicates. No use forcing a sort operation more than once. – Andomar May 14 '13 at 06:14
It worked for me...Also I want to know if I have more than two columns(Say 5 columns).... the 5 queries with union all is working, but is there any optimized method to do it..or I have to do by mentioning 5 queries for 5 columns – usr021986 May 14 '13 at 06:21
1

@Andomar : The query plan is same if we remove the outer distinct query and just use `union` in the inner query. I cannot see two sorts. – Ravi Singh May 14 '13 at 06:36
@RaviSingh: That would be right, even two distinct operations (like `disinct` and `union`) could probably reuse the same sort. I'll vote for your answer :) – Andomar May 14 '13 at 06:53
but i want two different columns not single column. – 151291 Nov 30 '15 at 06:47

score 2 · Answer 2 · edited May 23 '17 at 11:51

2

Please try:

Select Col1 from YourTable
union
Select Col2 from YourTable

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. Please check What is the difference between UNION and UNION ALL

For multiple columns, you can go for UNPIVOT.

SELECT distinct DistValues
FROM 
   (SELECT Col1, Col2, Col3
   FROM YourTable) p
UNPIVOT
   (DistValues FOR Dist IN 
      (Col1, Col2, Col3)
)AS unpvt;

edited May 23 '17 at 11:51

Community

1
1

answered May 14 '13 at 06:13

TechDo

18,398
3
51
64

2

Using `distinct` is not required in first solution. – Ravi Singh May 14 '13 at 06:29

score 2 · Answer 3 · answered May 14 '13 at 06:25

2

SQL Fiddle

Why even distinct in Union, try this :

select cast(id  as char(1)) from test
union
select val from test

answered May 14 '13 at 06:25

Ravi Singh

2,042
13
29

Didn't upvote this earlier because of the strange `cast`, but you are right about `union` – Andomar May 14 '13 at 06:53

score 1 · Answer 4 · answered May 14 '13 at 06:15

Try this one -

DECLARE @temp TABLE
(
      Col1 INT
    , Col2 NVARCHAR(50)
)

INSERT INTO @temp (Col1, Col2)
VALUES (1, 'ab5defg'), (2, 'ae4eii')

SELECT disword = (
    SELECT DISTINCT dt.ch
    FROM (
        SELECT ch = SUBSTRING(t.mtxt, n.number + 1, 1) 
        FROM [master].dbo.spt_values n
        CROSS JOIN (
            SELECT mtxt = (
                SELECT CAST(Col1 AS VARCHAR(10)) + Col2
                FROM @temp
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'
            )
        ) t
        WHERE [type] = N'p'
        AND number <= LEN(mtxt) - 1
    ) dt
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'
)

Or try this -

DECLARE @temp TABLE
(
    a CHAR(1), b CHAR(1)
)

INSERT INTO @temp (a, b)
VALUES 
    ('1', 'A'), ('1', 'B'), ('1', 'A'),
    ('1', 'B'), ('2', 'C'), ('2', 'C')

SELECT a
FROM @temp

UNION

SELECT b
FROM @temp

For multiple columns (varchar(...)) suitable use XML (see first example) - Col1 + Col2 + ... — Devart, May 14 '13 at 06:27
I cannot refrain to say that this answer is impressive - even frightening - by its unnecessary complexity. — iDevlop, May 14 '13 at 07:02

score 1 · Answer 5 · edited May 14 '13 at 06:32

1

Because what you want select is in different columns, you can use union like below:

select distinct tarCol from  
(select distinct column1 as tarCol from table
 union 
select distinct column2 from table) as tarTab

edited May 14 '13 at 06:32

Ravi Singh

2,042
13
29

answered May 14 '13 at 06:16

morgan117

338
1
5
16

score 0 · Answer 6 · edited Jul 08 '15 at 14:15

0

You can use like this to get multiple distinct column values

(SELECT DISTINCT `enodeb` as res, 
                 "enodeb" as columnname 
 FROM `raw_metrics`) 
UNION
(SELECT DISTINCT `interval` as res, 
       "interval" as columnname 
 FROM `raw_metrics`)

edited Jul 08 '15 at 14:15

Ram

3,092
10
40
56

answered Jul 08 '15 at 13:49

Ambarish

139
1
5

Select only distinct values from two columns from a table

6 Answers6