SQL create distinct comma separated lists

Question

I have a stored procedures that accepts a comma separated list, then makes copies of that list with quoted strings and brackets, then uses those in a dynamic sql statement to build a pivot table with flexible number of columns.

My problem is that sometimes my users submit a list with duplicates, and that makes the pivot query fail. So I want to somehow select distinct from my comma separated strings.

Here's how I manipulate the initial string:

Declare @data varchar(max) = '150593, 150593, 150603, 150578, 150604'

Declare @bracketed varchar(max) = ''
Declare @quoted varchar(max) = ''

select @bracketed = @bracketed + quotename(rtrim(ltrim(Value))) + ', ',  
      @quoted = @quoted + quotename(rtrim(ltrim(Value)), '''') + ', '
from [dbo].[fnSplitStringAsTable](@data, ',')

Select @bracketed = LEFT(@bracketed, len(@bracketed) - 1), 
       @quoted = LEFT(@quoted, len(@quoted) - 1)

I'm thinking I should be able to add DISTINCT somewhere in this query,
but I can't make it work. How can I select distinct from comma separated lists?

http://stackoverflow.com/questions/10298805/mimic-group-concat-combined-with-group-by — Aaron Bertrand, Jan 22 '15 at 21:39
@AaronBertrand - how do you remember answers from 3 years ago... I don't remember what I said last week. — Hogan, Jan 22 '15 at 21:40
@AaronBertrand -While I agree your memory is impressive, can you tell me which part of that post is relavent? I'm not seeing it... — MAW74656, Jan 22 '15 at 21:41
Sorry, I thought you were trying to make grouped concat lists. See [this](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/08/06/more-on-splitting-lists-custom-delimiter-preventing-duplicates-and-maintaining-order.aspx) for a function that removes duplicates. I suspect it is more efficient than your current function. — Aaron Bertrand, Jan 22 '15 at 21:44

score 4 · Answer 1 · answered Jan 22 '15 at 22:06

4

as an alternative solution you can dedupe in xml and convert back to varchar

Declare @data varchar(max) = '150593, 150593, 150603, 150578, 150604'
set @data= (select '''' + cast(cast('<d>'+replace(@data, ', ',',</d><d>')+'</d>'  as xml).query('distinct-values(/d)') as varchar) +'''')
select @data

answered Jan 22 '15 at 22:06

Jayvee

10,670
3
29
40

That's slick @Jayvee! – Ron Smith Jan 22 '15 at 22:15

Hogan · Accepted Answer · 2015-01-22T22:02:14.793

0

I guess we can add in distinct after you make the table, like this:

select @bracketed = @bracketed + quotename(rtrim(ltrim(Value))) + ', ',  
      @quoted = @quoted + quotename(rtrim(ltrim(Value)), '''') + ', '
from (
  SELECT DISTINCT Value FROM [dbo].[fnSplitStringAsTable](@data, ',')
) T

If this fails try this:

select @bracketed = @bracketed + quotename(Value) + ', ',  
      @quoted = @quoted + quotename(Value), '''') + ', '
from (
  SELECT DISTINCT RTRIM(LTRIM(Value)) AS Value FROM [dbo].[fnSplitStringAsTable](@data, ',')
) T

edited Jan 22 '15 at 22:02

answered Jan 22 '15 at 21:41

Hogan

69,564
10
76
117

-Tempting for its simplicity, but doesn't eliminate the duplicates. Also, needs an alias for the subquery. – MAW74656 Jan 22 '15 at 21:50
@MAW74656 - Why doesn't it eliminate the duplicates? Did it fail in your test? – Hogan Jan 22 '15 at 21:54
No clue.... but its not doing so. My test still throws the error `The column '150593' was specified multiple times for 'p'` – MAW74656 Jan 22 '15 at 21:56
@MAW74656 see edit. If that fails too you have to show us the actual code you are using. – Hogan Jan 22 '15 at 21:57
-I assume you meant ltrim(rtrim(value)). Now it works. Why though? – MAW74656 Jan 22 '15 at 22:00
1

@MAW74656 - yeah LTRIM+RTRIM, you can take those out of the select part now and get some value (pun intended). It works because your split function was leaving in extra whitespace somewhere and DISTINCT works. – Hogan Jan 22 '15 at 22:01

score 0 · Answer 3 · answered Jan 22 '15 at 21:59

0

With a little dynamic sql, you can select distinct values from your string variable into a table variable, and then put those values back into the original variable:

declare @data varchar(max) = '150593, 150593, 150603, 150578, 150604'
declare @table table(data varchar(10))

set @data = 'select distinct value from (values (''' +
        replace(@data,', ','''),(''') + ''')) as v(value)'

insert into @table
    exec(@data)

set @data = ''
select @data = @data + data +
    case row_number() over(order by data desc)
        when 1 then ''
        else ','
    end
from @table
order by data asc

select @data

answered Jan 22 '15 at 21:59

Ron Smith

3,241
1
13
16

He is already using a function to put the values into a table -- why not just manipulate that and not go crazy with string tricks? – Hogan Jan 22 '15 at 22:00
1

@RonSmith - Hogan is right, although I like your row_number trick for figuring out if you need a comma or not. – MAW74656 Jan 22 '15 at 22:05

SQL create distinct comma separated lists

3 Answers3