How to select distinct value from two columns into one column

Question

There are two location related columns in my table like below

| Service_No | A_LOC | Z_LOC |
|------------|-------|-------|
| 001        | A     | B     |
| 002        | A     | C     |
| 003        | Null  | C     |
| 004        | F     | B     |

How do I select the distinct values of columns A_LOC and Z_LOC combined into a single list? The result of this query would be:

A, B, C, F

What research have you done? What have you tried? Where did you get stuck? — Dale K, Jan 25 '21 at 20:25
@DaleK I found [this](https://stackoverflow.com/questions/22739841/mysql-combine-two-columns-into-one-column/22739860), but it is about concatinating columns — ddd, Jan 25 '21 at 20:27
@eshirvana I am not sure, how do I found out about the version — ddd, Jan 25 '21 at 20:30
Do you want separate rows for distinct values or all one value concatenated together? — Charlieface, Jan 25 '21 at 21:18

eshirvana · Accepted Answer · 2021-01-25T20:38:52.007

1

here is one way:

select string_Agg(Location,',') as distinct_location_list
 from (
select A_LOC Location FROM tablename
union 
select Z_LOC Location FROM tablename
) tt

edited Jan 25 '21 at 20:38

answered Jan 25 '21 at 20:35

eshirvana

23,227
3
22
38

I don't need to join with ',', but I think union does the trick. Does union take care of the duplicates when combining the two columns? – ddd Jan 25 '21 at 20:38
@ddd yes it eliminates duplicates – eshirvana Jan 25 '21 at 20:39
`Location` is a Keyword in sql server. So, it would be nice not to use that keyword as alias. :) – Srijon Chakraborty Jan 25 '21 at 20:41

score 1 · Answer 2 · answered Jan 25 '21 at 20:37

I think, I have a quick solution for you. However, you can optimize it or you can do it in different ways.

DECLARE @YourTable TABLE
(
Service_No VARCHAR(30),
A_LOC  VARCHAR(30),
Z_LOC  VARCHAR(30)
);
INSERT INTO  @YourTable Values('001','A','B');
INSERT INTO  @YourTable Values('002','A','C');
INSERT INTO  @YourTable Values('003',NULL,'C');
INSERT INTO  @YourTable Values('004','F','B');

DECLARE @TempTable TABLE(FINALDATA  VARCHAR(30));

INSERT INTO @TempTable
SELECT A_LOC FROM @YourTable WHERE A_LOC IS NOT NULL;
INSERT INTO @TempTable
SELECT Z_LOC FROM @YourTable WHERE Z_LOC IS NOT NULL;

SELECT DISTINCT FINALDATA FROM @TempTable;

Note: This code is written in SQL SERVER. Please check the code and let me know.

score 1 · Answer 3 · answered Jan 25 '21 at 20:49

1

Alternatively try this method if the string_agg function is not available.

select 
  STUFF(
         (SELECT ', ' + Loc FROM 
            (
            select distinct A_Loc  as Loc from @t 
            union 
            select distinct Z_Loc  as Loc from @t 
            ) t2 
          FOR XML PATH (''))
        , 1, 1, '')

answered Jan 25 '21 at 20:49

Johnny Fitz

532
2
8

This problem and the alternatives are well covered in https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv/42778050#42778050 – Johnny Fitz Jan 25 '21 at 20:49

score 1 · Answer 4 · answered Jan 25 '21 at 21:34

1

I would recommend unpivoting using apply and then filtering and distincting:

select string_agg(loc, ',')
from (select distinct loc
      from t cross apply
           (values (a_loc), (z_loc)) v(loc)
      where loc is not null
     ) v;

answered Jan 25 '21 at 21:34

Gordon Linoff

1,242,037
58
646
786

How to select distinct value from two columns into one column

4 Answers4