select distinct Id with different names asociated and select also the names related to the id selected :/

Question

My title sounded like a tongue twister, so let me explain myself better. I have a table with very bad data quality in which I have the following case:

CustomerId CustomerName
1          Jhon
1          Mary
2          Tom

CustomerId is not the key of the table.

I need to populate a Dictionary with the list but when I did the following:

select distinct CustomerId, CustomerName from FullTransactions

it returned the previous data set, and then when I tried to populate the combobox launched an exception because I am not allowed to repeat the same key.

Do you suggest any workaround to make a select distinct in which return a unique customerId, I don't mind if is selecting just one of the customernames or merging in a name every occurrence...

CustomerId CustomerName
1          Jhon-Mary
2          Tom

Hope I explained myself better now... Thank you very much in advance for any light you can bring...

sorry for the delay, I am using Microsoft SQL Server Management Studio 14.0.17285.0, thanks. — JudithMCA, Nov 28 '19 at 16:36

GMB · Accepted Answer · 2019-11-22T22:16:42.593

In SQL Server 2017, you can use aggregate function string_agg():

select 
    CustomerId , 
    string_agg(CustomerName, '-') within group (order by CustomerName) CustomerName
from mytable
group by CustomerId

In earlier versions, a solution is to use for xml path and stuff():

select distinct 
    t.CustomerId,
    stuff(
        (
            select distinct ',' + t1.CustomerName
            from mytable t1
            where t1.CustomerId = t.CustomerId
            for xml path(''), type
        ).value('.', 'nvarchar(max)'),
        1,
        0,
        ''
    ) CustomerName
from mytable t;

select distinct Id with different names asociated and select also the names related to the id selected :/

1 Answers1