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?

@AaronBertrand - how do you remember answers from 3 years ago... I don't remember what I said last week. — Hogan
– Hogan, Commented Jan 22, 2015 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
– MAW74656, Commented Jan 22, 2015 at 21:41
Sorry, I thought you were trying to make grouped concat lists. See this for a function that removes duplicates. I suspect it is more efficient than your current function. — Aaron Bertrand
– Aaron Bertrand, Commented Jan 22, 2015 at 21:44

Jayvee · Accepted Answer · 2015-01-22 22:06:27Z

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, 2015 at 22:06

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ron Smith Over a year ago

That's slick @Jayvee!

Hogan · Accepted Answer · 2015-01-22 22:02:14Z

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, 2015 at 22:02

answered Jan 22, 2015 at 21:41

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

6 Comments

MAW74656 Over a year ago

-Tempting for its simplicity, but doesn't eliminate the duplicates. Also, needs an alias for the subquery.

Hogan Over a year ago

@MAW74656 - Why doesn't it eliminate the duplicates? Did it fail in your test?

MAW74656 Over a year ago

No clue.... but its not doing so. My test still throws the error The column '150593' was specified multiple times for 'p'

Hogan Over a year ago

@MAW74656 see edit. If that fails too you have to show us the actual code you are using.

Hogan Over a year ago

@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.

|

Ron Smith · Accepted Answer · 2015-01-22 21:59:02Z

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, 2015 at 21:59

Ron Smith

3,2661 gold badge15 silver badges16 bronze badges

2 Comments

Hogan Over a year ago

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?

MAW74656 Over a year ago

@RonSmith - Hogan is right, although I like your row_number trick for figuring out if you need a comma or not.

Collectives™ on Stack Overflow

SQL create distinct comma separated lists

3 Answers 3

1 Comment

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related