I want to display records for one ID if there is more NUMBER for the same ID

Question

For the first ID which is 1265 I want only one record like this(look below), and for now I have three different records for the same ID and that isn't good. You can see that in the first record with ID = 1265 I would like to have concated strings into one separated with ',':

1265 MARK, LISA, MAY

Here is my code sample:

select 
    vup.UgovorId as ID, 
    concat(p.Naziv, p.Naziv) as NUMBER 
from 
    [TEST_Ugovori_Prod].dbo.VezaUgovorPartner as vup 
inner join 
    [TEST_MaticniPodaci2].[dbo].[Partner] as p on vup.PartnerId = p.PartnerID 
group by 
    vup.UgovorId, p.Naziv
order by 
    vup.UgovorId desc

Here are my results:

1265    MARK  
1265    LISA
1265    MAY
1264    LINDA  
1263    MARTINA
1262    MARKO                                                                                                                                                                                
1261    VIDAKS
1260    PEEKS 
1259    MARCUS
1258    MARKO                                                                                                                                                                                
1257    MATIA                                                                                                                                                                                 
1256    POPOVIC

The `concat(p.Naziv, p.Naziv)` seems like a mistake in your example. The results you've provided would have been `MARKMARK`, `LISALISA`, etc.. if that was what you actually used. Seems you could take that out of your example query. — Brien Foss, Feb 24 '18 at 21:03
Possible duplicate of [How to show all names for identical contract in T-SQL](https://stackoverflow.com/questions/48965582/how-to-show-all-names-for-identical-contract-in-t-sql) — paparazzo, Feb 25 '18 at 15:41

Brien Foss · Accepted Answer · 2018-02-24T21:36:12.530

SELECT ID, NUMBER = 
    STUFF((SELECT DISTINCT ', ' + NUMBER
           FROM example t2 
           WHERE t2.ID = t1.ID
          FOR XML PATH('')), 1, 2, '')
FROM example t1
GROUP BY ID
ORDER BY ID DESC

[DEMO HERE]

If you are having trouble blending with your query above, this should help:

SELECT vup.UgovorId as ID, 
     STUFF((SELECT DISTINCT ', ' + p2.Naziv  
           FROM [TEST_Ugovori_Prod].dbo.VezaUgovorPartner vup2 
           INNER JOIN [TEST_MaticniPodaci2].[dbo].[Partner] p2 ON vup2.PartnerId = p2.PartnerID 
           WHERE vup2.UgovorId = vup.UgovorId   
           FOR XML PATH('')), 1, 2, '') NUMBER
FROM [TEST_Ugovori_Prod].dbo.VezaUgovorPartner vup 
INNER JOIN [TEST_MaticniPodaci2].[dbo].[Partner] p on vup.PartnerId = p.PartnerID 
GROUP BY vup.UgovorId
ORDER BY vup.UgovorId DESC

[Test blend]

I want to display records for one ID if there is more NUMBER for the same ID

1 Answers1