Merge rows in SQL Server 2012 and have data separated by comma or minus

Question

I wrote this query:

SELECT ALL
    RTrim(ARTICO.CODICE) AS [Cod.Ricambio],
    RTrim(ARTICO.DESCR) AS Descrizione,
    RTrim(ARTCLA4.CODICE) AS [Modello Princ.],
    RTrim(ARTICO1.CODICE) AS [Cod.Compatib.]
FROM
    ARTICO
LEFT JOIN 
    ARTSOS ON ARTSOS.IDARTICO = ARTICO.ID
LEFT JOIN 
    ARTICO ARTICO1 ON ARTSOS.IDARTSOS = ARTICO1.ID
LEFT JOIN 
    ARTCLA4 ON ARTCLA4.ID = ARTICO.IDARTCLA4
WHERE
    ARTICO.IDLINEEP = 4

ARTICO.CODICE can have duplicates because even if articla4.codice is always the same (related to artico.codice), artico1.codice can have multiple values.

I.E. Can be so

artico.codiceABC        articla4.codice001       artico1.codice001
artico.codiceABC        articla4.codice001       artico1.codice002
artico.codiceABC        articla4.codice001       artico1.codice003

and the result I'd like to get is this:

artico.codiceABC    articla4.codice001    artico.codice001,artico.codice002,artico.codice003

Your current select mentions _four_ columns, but the current output only appears to have three. Can you explain this? — Tim Biegeleisen, May 21 '19 at 15:21
Yes, the second column is not relevant, it is the description of the article, it can be not considered. Thanks — Assistenza Newmobi, May 21 '19 at 15:23

score 0 · Answer 1 · answered May 21 '19 at 15:27

0

Since you are not using SQL Server 2017 or later, we can try using the standard approach to simulate group concatenation:

SELECT
    RTRIM(ARTICO.CODICE) AS [Cod.Ricambio],
    RTRIM(ARTICO.DESCR) AS Descrizione,
    RTRIM(ARTCLA4.CODICE) AS [Modello Princ.],
    [Cod.Compatib.] = STUFF((
        SELECT ',' + ARTICO1.CODICE
        FROM ARTICO ARTICO1
        WHERE ARTSOS.IDARTSOS = ARTICO1.ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM ARTICO
LEFT JOIN ARTSOS
     ON ARTSOS.IDARTICO = ARTICO.ID
LEFT JOIN ARTCLA4
    ON ARTCLA4.ID = ARTICO.IDARTCLA4
WHERE
    ARTICO.IDLINEEP = 4;

answered May 21 '19 at 15:27

Tim Biegeleisen

502,043
27
286
360

I have tried but unfortunately it does not work :( I have got the same result – Assistenza Newmobi May 21 '19 at 15:31
Without seeing sample data, or better yet a demo, this is the best I can do. I will leave this up for a while to maybe inspire someone else to get it right. – Tim Biegeleisen May 21 '19 at 15:32
You are right, excuse me if i wasn't able to explain better my problem. I will do my best for send a better example. Anyway, the right field is idartsos that is related to artico1.id but i want to see artico1.codice. How can I show a demo or some sample datas? – Assistenza Newmobi May 21 '19 at 15:42

score 0 · Answer 2 · answered May 23 '19 at 15:39

a friend of mine has told me one solution and it's ok but unfortunately it left a comma as last character. Ho can I remove this?

SELECT DISTINCT 
    RTrim(ARTICO.CODICE) AS [Cod.Ricambio],
    RTrim(ARTICO.DESCR) AS Descrizione,
    RTrim(ARTCLA4.CODICE) AS [Modello Princ.],
    (
SELECT  RTrim(ARTICO1.CODICE) + ','  AS 'data()' 
FROM
    ARTICO A
LEFT JOIN 
    ARTSOS ON ARTSOS.IDARTICO = ARTICO.ID
LEFT JOIN 
    ARTICO ARTICO1 ON ARTSOS.IDARTSOS = ARTICO1.ID
LEFT JOIN 
    ARTCLA4 ON ARTCLA4.ID = ARTICO.IDARTCLA4
WHERE
    ARTICO.IDLINEEP = 4 AND A.CODICE = ARTICO.CODICE 
 FOR XML PATH('') 
) AS [Cod.Compatib.]
FROM
    ARTICO
LEFT JOIN 
    ARTSOS ON ARTSOS.IDARTICO = ARTICO.ID
LEFT JOIN 
    ARTICO ARTICO1 ON ARTSOS.IDARTSOS = ARTICO1.ID
LEFT JOIN 
    ARTCLA4 ON ARTCLA4.ID = ARTICO.IDARTCLA4
WHERE
    ARTICO.IDLINEEP = 4

Merge rows in SQL Server 2012 and have data separated by comma or minus

2 Answers2