CODEWITHSUNDEEP
sqlsql-servercsvpivot
MAW74656
MAW74656

Reputation: 3539

SQL create distinct comma separated lists

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?

Upvotes: 0

Views: 5527

Answers (3)

Jayvee
Jayvee

Reputation: 10875

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

Upvotes: 4

Hogan
Hogan

Reputation: 70523

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

Upvotes: 0

Ron Smith
Ron Smith

Reputation: 3266

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

Upvotes: 0

Related Questions