Using Distinct and COALESCE Together using variable

Question

I Have a table with below data

column1   column2

DIU02   3D ITEM MAINTENANCE
DIU02   DISTRIBUTION ITEM UPDATE APPLICATION
DIU02   DIU - Distribution Item Update

I want for unique DIU02 , all data come in single column using comma seperation.

Its for SQL Server Database

Below is the Query

DECLARE @Data VARCHAR(MAX)

SELECT @Data = COALESCE(@Data + ',', '') + column2
FROM Table
WHERE column1= 'DIU02'

I want to get only distinct data and I used distinct keyword but I am getting only one.

Please help !

possible duplicate of [How to make a query with group\_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — Raphaël Althaus, Jul 15 '14 at 16:29
@RaphaëlAlthaus But that particular syntax of `@var = @var + column` can in fact perform conacatenation — Lamak, Jul 15 '14 at 16:31

score 2 · Accepted Answer · answered Jul 15 '14 at 16:38

2

SELECT @Data = COALESCE(@Data + ',', '') + column2
FROM (
  SELECT DISTINCT column2
  FROM Table
  WHERE column1= 'DIU02'
) t

answered Jul 15 '14 at 16:38

Anon

10,660
1
29
31

Fabian Bigler · Answer 2 · 2014-07-15T18:13:18.750

0

I bet you're looking for that one (MS SQL):

DECLARE @List VARCHAR(8000)

SELECT @List = COALESCE(@List + ',', '') + CAST(column2 AS VARCHAR)
FROM   yourtable
WHERE  column1 = 'DIU02'

SELECT @List

SQL Fiddle

edited Jul 15 '14 at 18:13

answered Jul 15 '14 at 16:29

Fabian Bigler

10,403
6
47
70

score 0 · Answer 3 · answered Jul 15 '14 at 16:33

0

Maybe this, if I understand you

select  col1,Stuff=Stuff((SELECT ',' + col2  FROM t t1 WHERE t1.col1=t.col1   

 FOR XML PATH (''))
             , 1, 1, '' )
from t
GROUP BY col1

Fiddle

answered Jul 15 '14 at 16:33

Mihai

26,325
7
66
81

Using Distinct and COALESCE Together using variable

3 Answers3