I have a table Articles where same the article may have different Id (ArtId).

What I'm trying to achieve is to merge the same article rows into one, based on ArtNo column, and to CONCAT the ArtId column values into a new columns ArtIds, separated by comma.

If there is one article in the table the new column should be just that ArtId and no comma after that.

I try the following, but without result on what I want to achieve:

DECLARE @tbl TABLE
             (
                 ArtId INT, 
                 ArtName VARCHAR(100), 
                 ArtNo VARCHAR(50)
             );

INSERT INTO @tbl (ArtId, ArtName, ArtNo)
    SELECT 100, 'Red Dress', '0123456' UNION ALL
    SELECT 110, 'Red Dress', '0123456' UNION ALL
    SELECT 220, 'Red Dress', '0123456' UNION ALL
    SELECT 308, 'Red Dress', '0123456' UNION ALL
    SELECT 605, 'Red Dress', '0123456' UNION ALL
    SELECT 999, 'Red Dress', '0123456' UNION ALL
    SELECT 1110, 'Green Dress', '123455' UNION ALL
    SELECT 1200, 'Purple Dress', '987654' UNION ALL
    SELECT 1300, 'Purple Dress', '987654'
 

SELECT DISTINCT
    *
FROM
    (SELECT 
         t2.ArtIds,
         t2.ArtNo AS ArtNoT2,
         t1.ArtName,
         t1.ArtNo
     FROM 
         @tbl t1
     INNER JOIN
        (SELECT 
             at2.ArtNo,
             SUBSTRING((SELECT ','   CAST(at1.ArtId AS VARCHAR) AS [text()]
                        FROM @tbl at1
                        WHERE at1.ArtId != at2.ArtId
                          AND at1.ArtNo = at2.ArtNo
                        ORDER BY at1.ArtId
                        FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)'), 2, 1000) [ArtIds]
         FROM @tbl at2) t2 ON t2.ArtNo = t1.ArtNo
    ) Z
GROUP BY 
    Z.ArtIds, Z.ArtNo, Z.ArtNoT2, Z.ArtName, Z.ArtNo;

But I get this result:

Can you help me to achieve what I'm looking for?

Thank you in advance

CodePudding user response：

If on Azure or on v2017 or later, try something like:

SELECT ArtNo, STRING_AGG(ArtId, ', ') AS IDs
FROM YourTable
GROUP BY ArtNo ;

CodePudding user response：

Here' s a sultion for older versions of Sql Server.

DECLARE @tbl TABLE
             (
                 ArtId INT, 
                 ArtName VARCHAR(100), 
                 ArtNo VARCHAR(50)
             );

INSERT INTO @tbl (ArtId, ArtName, ArtNo)
    SELECT 100, 'Red Dress', '0123456' UNION ALL
    SELECT 110, 'Red Dress', '0123456' UNION ALL
    SELECT 220, 'Red Dress', '0123456' UNION ALL
    SELECT 308, 'Red Dress', '0123456' UNION ALL
    SELECT 605, 'Red Dress', '0123456' UNION ALL
    SELECT 999, 'Red Dress', '0123456' UNION ALL
    SELECT 1110, 'Green Dress', '123455' UNION ALL
    SELECT 1200, 'Purple Dress', '987654' UNION ALL
    SELECT 1300, 'Purple Dress', '987654'

select  (stuff((select ','   cast(ArtId as varchar) 
                from @tbl t2
                where t.ArtName = t2.ArtName
                for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '')) as ArtIds
        ,ArtName
        ,ArtNo

from     @tbl t
group by ArtName, ArtNo

Fiddle