Need distinct comma delimited product values order by date

Question

I have below table structure

create TABLE PRODUCTDeatils 
(
    Product varchar(50),
    Date Datetime
)

I want an output where i get comma separated list with distinct values but order by date.

I first split the values by below query

SELECT DISTINCT
    LTRIM(RTRIM(value)) AS Product, date
INTO #tmp3
FROM PRODUCTDeatils
CROSS APPLY STRING_SPLIT(LTRIM(RTRIM(Product)), ',')    

SELECT * 
FROM #tmp3 
ORDER BY date

Then I used below two queries to achieve it but not successful.

Query #1:

DECLARE @cols AS NVARCHAR(MAX);

SELECT @COLS =  substring(list, 1, LEN(list) - 1)
FROM   
    (SELECT 
         list = (SELECT DISTINCT Product + ';'
                 FROM #tmp3
                 ORDER BY Product + ';'
                 FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')) AS T

SELECT @COLS

Query #2:

 SELECT  
     STUFF((SELECT '; ' + Product
            FROM 
                (SELECT DISTINCT TOP 100
                     Product,date
                 FROM #tmp3) x  
            ORDER BY date
            FOR XML PATH('')), 1, 2, '') Text

I want output like above but bat should only come once.

Instead of DISTINCT switch to aggregation: `LTRIM(RTRIM(value)) AS Product, MIN(date)` in query #2. — dnoeth, May 17 '20 at 07:23
Normalize the schema. Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|193.2144). — sticky bit, May 17 '20 at 07:23

score 1 · Answer 1 · answered May 17 '20 at 12:13

In the more recent versions of SQL Server, you should just use string_agg():

SELECT STRING_AGG(Product, ',') WITHIN GROUP (ORDER BY date)
FROM (SELECT LTRIM(RTRIM(s.value)) AS Product, MIN(date) as date
      FROM ProductDetails pd CROSS APPLY
           STRING_SPLIT(LTRIM(RTRIM(pd.Product)), ',') s 
      GROUP BY LTRIM(RTRIM(s.value))
     ) p

Here is a db<>fiddle.

score 0 · Answer 2 · answered May 17 '20 at 07:50

You can add row_number and select only one value per product.

SELECT DISTINCT
    LTRIM(RTRIM(value)) AS Product, date, ROW_NUMBER() over (partition by trim(Value) order by date) rn
INTO #tmp3
FROM PRODUCTDeatils
CROSS APPLY STRING_SPLIT(LTRIM(RTRIM(Product)), ',')    

SELECT  
     STUFF((SELECT '; ' + Product
            FROM 
                (SELECT DISTINCT TOP 100
                     Product,date
                 FROM #tmp3 where rn = 1) x  
            ORDER BY date
            FOR XML PATH('')), 1, 2, '') Text

Please find the db<>fiddle here.

Need distinct comma delimited product values order by date

2 Answers2