Q:

remove duplicate from result in sql

I have to combine values from two query and display as comma seperated and need to get distinct out of it. I tried to use join/union all or use distinct in each value but still i get duplicates if the values are common can someone please help with this. Below is my query and output
SELECT DISTINCT
    (SELECT DISTINCT
         ISNULL((SELECT TOP 1 PV.serialnumber 
                 FROM ConsumerItemsTable PV 
                 WHERE PV.ItemID = 61) + ',' , '')  +
         ISNULL(STUFF((SELECT distinct ',' + itemid   
                       FROM CustomerItems b  
                       WHERE Name = 'WillamRobbe'
                       FOR XML PATH('')), 1, 1, '')  + ',' , ''))

Output of first query is 0991345
Output of second query is 0033874,0991345,8877425

Expected output is 0033874,0991345,8877425
Current output is 0991345,0033874,0991345,8877425

A:

Here is one way using union and moving the outer select inside the for xml query using a derived table:
select stuff((
    select ',' + itemid
    from (
        select itemid
        from CustomerItems b  
        where Name = 'WillamRobbe'
        union 
        select top 1 serialnumber
        from ConsumerItemsTable pv 
        where pv.Itemid = 61
      ) s
    for xml path('') 
   ), 1, 1, '')

rextester demo: http://rextester.com/UXIK17206
returns: 0033874,0991345,8877425

