Concatenated distinct values with column names

Question

What is the most efficient way to get a concatenated list of distinct values from selected columns on SQL Server?

Col1    Col2    Col3 

A       3       C23

A       3       C32

B       2       D12

C       1       C23

D       5       D12

D       5       C32

My target output is a table as follows:

ColumnNames        DistinctValues

Col1              {A,B,C,D}

Col2              {1,2,3,5}

Col3              {C23,C32,D12}

Would be great if I could specify which columns to be looked up.

[Read here](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) for a start. — Tim Biegeleisen, Jan 17 '18 at 01:38
Possible duplicate of [SQL Server convert select a column and convert it to a string](https://stackoverflow.com/questions/16193152/sql-server-convert-select-a-column-and-convert-it-to-a-string) — ViKiNG, Jan 17 '18 at 02:14

Pரதீப் · Accepted Answer · 2018-01-17T05:26:37.350

First you need to unpivot the data

SELECT * 
FROM   yourtable 
       CROSS apply (VALUES(col1,'Col1'), 
                          (Cast(col2 AS VARCHAR(50)),'Col2'), 
                          (col3,'Col3')) tc(val, columnnames)

then concatenate based on the column names to get the result

;WITH cte 
     AS (SELECT * 
         FROM   yourtable 
                CROSS apply (VALUES(col1,'Col1'), 
                                   (Cast(col2 AS VARCHAR(50)),'Col2'), 
                                   (col3, 'Col3')) tc(val, columnnames)) 
SELECT DISTINCT columnnames, 
                '{'+Stuff(distinctvalues, 1, 1, '')+'}'
FROM   cte a 
       CROSS apply(SELECT DISTINCT ',' + val 
                   FROM   cte b 
                   WHERE  a.columnnames = b.columnnames 
                   FOR xml path('')) cs (distinctvalues)

Demo

Another approach

SELECT 'Col1', 
       Stuff((SELECT DISTINCT ',' + col1 
              FROM   yourtable 
              FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 
UNION ALL 
SELECT 'Col2', 
       Stuff((SELECT DISTINCT ',' + Cast(col2 AS VARCHAR(50)) 
              FROM   yourtable 
              FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 
UNION ALL 
SELECT 'Col3', 
       Stuff((SELECT DISTINCT ',' + col3 
              FROM   yourtable 
              FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Sorry brand new to sql and to stackoverflow questioning, done! — Kam, Jan 17 '18 at 03:53
Ah ... sorry but for large tables this is way slower than running individual distinct queries :( — Kam, Jan 17 '18 at 04:59
@Kam- This is the best the possible approach I can think of.. Group Concatenation isn't the strongest part of sql server. Another issue is we are unpivoting the data before concatenating to get the result in desired format .. — Pரதீப், Jan 17 '18 at 05:21
@P???? thank you for your help. I think that is the way to go -- get the results and then format the output. BTW, is there a way to wrap it in a loop (with some tests to identify the data type) instead of writing repetitive statements? — Kam, Jan 17 '18 at 21:06

Concatenated distinct values with column names

1 Answers1