SQL SELECT concatenate distinct column values in comma separated value-CodePudding

I have a Table containing orders details. Each row of this table is such an item of an order, and each one has a column named services, that's a comma separated list of services related to that item.

ID	order_id	services
1	1	123
2	1	123,456
3	2	456,789
4	2	123
5	2	789,456

What i reached is the seguent result

Order_Services
123,123,456

obtained with this query

SELECT STUFF(
    (SELECT DISTINCT ','   services
        FROM order_detail
        WHERE order_id = 1
        FOR XML PATH ('')), 1, 1, '')  AS Order_Services

I'd like to obtain a result set of concatenated services from a single order_id, but i want to remove duplicate too. The distinc in the STUFF is pretty useless cause it remove only equal columns (eg doesn't remove one of the value '123,456' and '456,123' cause aren't the same thing)

My expected result is,

for order_id=1

Order_Services
123,456

for order_id=2

Order_Services
123,456,789

Is there a simple way to achieve this?

My SQL version is:

DBFiddle

or SQL code for replicate the example

CREATE TABLE order_detail ( ID INT  IDENTITY(1,1) PRIMARY KEY, order_id INT, services varchar(100));

INSERT INTO order_detail (order_id,services) VALUES (1, '123' ), (1, '123,456' ), (2, '456,789' ),(2,'123'),(2,'789,456')


SELECT 
    STUFF(
         (SELECT DISTINCT ','   services
          FROM order_detail
          WHERE order_id = 1
          FOR XML PATH (''))
          , 1, 1, '')  AS Order_Services

CodePudding user response：

You can do this in SQL Server 2005 with a split function, e.g. from this post:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       nvarchar(max),
   @Delimiter  nvarchar(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value(N'(./text())[1]', N'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
            REPLACE(@List, @Delimiter, '</i><i>') 
            '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

Then you can say:

;WITH cte AS 
(
  SELECT d.order_id, f.Item
  FROM dbo.order_detail AS d
  CROSS APPLY dbo.SplitStrings_XML(d.services, ',') AS f
  GROUP BY d.order_id, f.Item
),
sub AS
(
  SELECT cte.order_id, Order_Services = STUFF(
    (SELECT ','   x.Item
      FROM cte AS x
      WHERE x.order_id = cte.order_id
      FOR XML PATH(''), 
      TYPE).value(N'./text()[1]', N'varchar(max)'), 
      1, 1, '')
  FROM cte
)
SELECT order_id, Order_Services 
  FROM sub
  GROUP BY order_id, Order_Services;

Output:

order_id	Order_Services
1	123,456
2	123,456,789

If you think, "wow, that's a really ugly and complicated query!" You're right. Modern versions of SQL Server support much more elegant approaches, and properly normalized data wouldn't require any such acrobatics in any version the first place.

The approach in SQL Server 2017 requires no custom function or messy XML handling in either direction:

;WITH cte AS
(
  SELECT d.order_id, s.value
    FROM dbo.order_detail AS d
    CROSS APPLY STRING_SPLIT(d.services, ',') AS s
    GROUP BY d.order_id, s.value
)
SELECT order_id, STRING_AGG(value, ',')
  FROM cte
  GROUP BY order_id;

Example db<>fiddle