CODEWITHSUNDEEP
sqlconcatenationdistinct
Fawad Shah
Fawad Shah

Reputation: 1784

Concatenating distinct column values to a string

I want to select distinct combinations of columns to a string. I do not know how to do it in a query. The scenario is as under

c1   c2   c3
a     1    x
b     2    x
b     2    y

I want a resultset like

a:1:x
a:1:y
a:2:x
a:2:y
b:1:x
b:1:y
b:2:x
b:2:y

Any suggestions on how to do it?

Upvotes: 1

Views: 2262

Answers (3)

Imre L
Imre L

Reputation: 6249

select concat(c1,':',c2,':',c3) from 
(select distinct c3 from t) as t3, 
(select distinct c2 from t) as t2, 
(select distinct c1 from t) as t1

Upvotes: 1

Ian Yates
Ian Yates

Reputation: 1354

Sample CREATE TABLE statement

create table #Test( c1 char(1), c2 char(1), c3 char(1) )
insert INTO #Test
SELECT
    'a', '1', 'x'
UNION ALL SELECT
    'b', '2', 'x'
UNION ALL SELECT
    'b', '2', 'y'

Combinations of all distinct column values

select 
    c1List.c1, c2List.c2, c3List.c3
from ( 
    select DISTINCT c1 from #Test ) c1List
CROSS JOIN (
    select DISTINCT c2 from #Test ) c2List
CROSS JOIN (
    select DISTINCT c3 from #Test ) c3List

String concatenations

select 
    c1List.c1 + ':' + c2List.c2 + ':' + c3List.c3
from ( 
    select DISTINCT c1 from #Test ) c1List
CROSS JOIN (
    select DISTINCT c2 from #Test ) c2List
CROSS JOIN (
    select DISTINCT c3 from #Test ) c3List

Upvotes: 3

kazatca
kazatca

Reputation: 289

select concat(c1,':',c2,':',c3) from ...

Upvotes: -1

Related Questions