How to get distinct in a variable

Question

Declare @var Nvarchar (500)

SELECT @var = 'EQUITY,EQUITY,EQUITY,EQUITY,EQUITY,EQUITY,EQUITY,DEBT,DEBT,DEBT,DEBT,DEBT,DEBT,
DEBT,DEBT,DEBT,DEBT,DEBT,DEBT'

SELECT DISTINCT @var

Loop it up into a table variable, then distinct-query that table variable. — KtX2SkD, Aug 03 '17 at 07:32

score 1 · Accepted Answer · answered Aug 03 '17 at 07:33

It can be done in three steps

Split the comma separated values into individual rows
Apply distinct on step 1
Concatenate the result back to comma separated values

Query Links to do it

Step 1: Turning a Comma Separated string into individual rows

Step 2 : Select Distinct from above result

Step 3 : Simulating group_concat MySQL function in Microsoft SQL Server 2005?

score 0 · Answer 2 · answered Aug 03 '17 at 07:48

0

You can find a solution here, https://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/

answered Aug 03 '17 at 07:48

Sushmit Patil

1,335
1
14
26

Links get stale. Could you summarize the solution? – DevSolar Sep 01 '20 at 17:41

score 0 · Answer 3 · answered Aug 03 '17 at 08:06

Here is a solution for solving this problem using in-memory tables.

    DECLARE @var NVARCHAR(500)
    SELECT @var = 'EQUITY,EQUITY,EQUITY,EQUITY,EQUITY,EQUITY,EQUITY,DEBT,DEBT,DEBT,DEBT,DEBT,DEBT,DEBT,DEBT,DEBT,DEBT,DEBT,DEBT'

    DECLARE @tempTable TABLE (items NVARCHAR(500)) 
    DECLARE @distinctTable TABLE (items NVARCHAR(500)) 
    DECLARE @delimiter CHAR(1) = ',' --- Specify the delimiter
    DECLARE @idx INT     
    DECLARE @section NVARCHAR(500)     
    DECLARE @ouput NVARCHAR(500)

    SELECT @idx = 1     
        IF LEN(@var)<1 OR @var IS NULL  RETURN     

    WHILE @idx!= 0     
    BEGIN     
        SET @idx = CHARINDEX(@delimiter,@var)     
        IF @idx!=0     
            SET @section = LEFT(@var,@idx - 1)     
        ELSE     
            SET @section = @var    

        IF(LEN(@section)>0)
            INSERT INTO @tempTable(Items) VALUES(@section)     

        SET @var = RIGHT(@var,LEN(@var) - @idx)     
        IF LEN(@var) = 0 BREAK     
    END 

    INSERT @distinctTable (items) 
    SELECT DISTINCT * FROM @tempTable

    SET @ouput = ''
    SELECT @ouput = @ouput + items + @delimiter FROM @distinctTable
    SELECT SUBSTRING(@ouput, 0, LEN(@ouput))

How to get distinct in a variable

3 Answers3