Is there any way to delete duplicates value in one row?-CodePudding

I have a table name deployment in which show the id of the deployed personal base on their task as columns the problem is 1 person can be deployed in multiple task and when I select the table is show as followed:

date | task1 | task2 | task3 | task4 
----- ------- ------- ------- ------
fri    5046    5048    5048    5048
sat    5046    5049    5048    5048
sun    5045    5047    5047    5049

I wanted to get the ids of personals in one date with out the duplicate as showed:

date | ids
----- ----
fri    5046 ,5048  
sat    5046 ,5048 ,5049
sun    5045 ,5047 ,5049

The only way I manage to it is to write a function as showed:

CREATE FUNCTION dbo.Idsdup
( @date date )
returns
nvarchar (max)
AS
BEGIN 
DECLARE @Listid = nvarchar (max)
SELECT @Listid = concat( ts.pert1_id, ';' , ts.pert2_id, ';' , ts.pert3_id, ';' , ts.pert4_id) FROM  deployment AS ts WHERE ts.date = @date

DECLARE @ParsedList TABLE
(
Item nvarchar(MAX)
)
INSERT into @ParsedList (Item)
SELECT distinct value  from string_split(@Listid , ',')
DECLARE @rList nvarchar(MAX)
SELECT @rlist = (select STRING_AGG(item,';') from @ParsedList)

RETURN @rlist
END

Then select liked showed:

select dp.date, dbo.Idsdup( dp.date) from deployment as dp

CodePudding user response：

A multi-line table value function would be a poor way to do this; it'll be awfully slow. An easier way would be to use a VALUES table construct to unpivot the data and get the DISTINCT values from that. Then you can simply aggregate the string using STRING_AGG:

SELECT V.[date],
       STRING_AGG(T.Task,' ,') WITHIN GROUP (ORDER BY T.Task) AS ids
FROM (VALUES('fri ',5046 ,5048 ,5048 ,5048),
            ('sat ',5046 ,5049 ,5048 ,5048),
            ('sun ',5045 ,5047 ,5047 ,5049))V(date,task1,task2,task3,task4)
     CROSS APPLY (SELECT DISTINCT t.task
                  FROM (VALUES(task1),(task2),(task3),(task4))t(task)) T
GROUP BY V.[date];