Remove duplicated values from the values in a row i sql

Question

My input code is like,

Emp_ID       Scenarios
10902543     SCENARIO 3, SCENARIO 1, SCENARIO 1, SCENARIO 4
11083080     SCENARIO 3, SCENARIO 1, SCENARIO 3
10988779     SCENARIO 4, SCENARIO 1, SCENARIO 4, SCENARIO 3

and want output should be,

Emp_ID       Scenarios
10902543    SCENARIO 3, SCENARIO 1, SCENARIO 4
11083080    SCENARIO 1, SCENARIO 3
10988779    SCENARIO 1, SCENARIO 4, SCENARIO 3

Need to do it without while loop, can you please suggest any other alternative.

Yes. First, read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!**. Second, read Jeff Moden's [The "Numbers" or "Tally" Table: What it is and how it replaces a loop](http://www.sqlservercentral.com/articles/T-SQL/62867/). — Zohar Peled, Jun 23 '17 at 06:31
select * from ##SCENARIOS_BASE EMP_ID COMMENTS 10016351 SCENARIO 4 10016351 SCENARIO 1 10016351 SCENARIO 4 10016351 SCENARIO 4 10048404 SCENARIO 3 10684877 SCENARIO 1 10048404 SCENARIO 1 SELECT EMP_ID ,STUFF((SELECT ', ' + CAST(COMMENTS AS VARCHAR(10)) [text()] FROM ##SCENARIOS_BASE WHERE EMP_ID = T.EMP_ID FOR XML PATH(''), TYPE) .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output FROM ##SCENARIOS_BASE T GROUP BY EMP_ID — user3620085, Jun 23 '17 at 06:46

TriV · Answer 1 · 2017-06-23T06:42:57.640

First use a SplitString function, then Distinct scenario value, and finally join them by For xml path + STUFF

DECLARE @SampleData AS TABLE
(
 Emp_ID int,
 Scenarios varchar(200)   
)

INSERT INTO @SampleData
(
    Emp_ID,
    Scenarios
)
VALUES
( 10902543, 'SCENARIO 3, SCENARIO 1, SCENARIO 1, SCENARIO 4'),
( 11083080, 'SCENARIO 3, SCENARIO 1, SCENARIO 3'),
( 10988779, 'SCENARIO 4, SCENARIO 1, SCENARIO 4, SCENARIO 3')

SELECT sd.Emp_ID, s.Scenarios
FROM @SampleData sd
CROSS APPLY
(
    SELECT STUFF((  
                SELECT DISTINCT ', '+ s.Value
                FROM [dbo].[SplitString](sd.Scenarios, ',') s
                FOR XML PATH('')
             )
    , 1,2,'') AS Scenarios

) s

Split function

CREATE FUNCTION [dbo].[SplitString] (@Text varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
   Select Pos = Row_Number() over (Order By (Select null))
        ,Value = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
   From (Select x = Cast('<x>'+ Replace(@Text,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
   Cross Apply x.nodes('x') AS B(i)
);

Returns

Emp_ID      Scenarios
-----------------------------------------------
10902543    SCENARIO 1, SCENARIO 3, SCENARIO 4
11083080    SCENARIO 1, SCENARIO 3
10988779    SCENARIO 1, SCENARIO 3, SCENARIO 4

I was just starting to write an answer very much like yours... +1. Just one thing, the `stuff` function doesn't join them, that's the `for xml path('')`. All the `stuff` does is remove the first comma. — Zohar Peled, Jun 23 '17 at 06:39

score 1 · Answer 2 · answered Jun 23 '17 at 06:53

Another way of doing with XML methods

Schema:

CREATE TABLE #TAB(Emp_ID BIGINT, Scenarios VARCHAR(MAX))

INSERT INTO #TAB

SELECT 10902543,'SCENARIO 3, SCENARIO 1, SCENARIO 1, SCENARIO 4'
UNION ALL
SELECT 11083080,'SCENARIO 3, SCENARIO 1, SCENARIO 3'
UNION ALL
SELECT 10988779,'SCENARIO 4, SCENARIO 1, SCENARIO 4, SCENARIO 3'

Now Split Scenarios, apply Distinct & add them together with comma.

;WITH CTE AS(
    SELECT DISTINCT Emp_ID,  SPLT.B.value('.','varchar(max)') Scenarios 
    FROM(
        SELECT Emp_ID, Scenarios
        ,CAST( '<M>'+REPLACE(Scenarios,', ','</M><M>')+'</M>' AS XML) XML_DATA
        FROM #TAB
    )A
    CROSS APPLY
    A.XML_DATA.nodes('/M') AS SPLT(B)
)
SELECT DISTINCT Emp_ID
, STUFF((SELECT ','+Scenarios
FROM CTE C1 
WHERE C1.Emp_ID = C2.Emp_ID FOR XML PATH(''),TYPE
).value('.','varchar(max)'),1,1,'') as Scenarios FROM CTE C2

Result:

+----------+----------------------------------+
|  Emp_ID  |            Scenarios             |
+----------+----------------------------------+
| 10902543 | SCENARIO 1,SCENARIO 3,SCENARIO 4 |
| 10988779 | SCENARIO 1,SCENARIO 3,SCENARIO 4 |
| 11083080 | SCENARIO 1,SCENARIO 3            |
+----------+----------------------------------+

Remove duplicated values from the values in a row i sql

2 Answers2