CODEWITHSUNDEEP
asp.netsql-serversql-server-2008
aamankhaan
aamankhaan

Reputation: 491

How to replace a name in the datatable using sql server

i want to replace the repeated customer name in the list of record in sql server

below is how the query result is...

CustomerName      ||      Product

CustomerA         ||      Product    X
CustomerA         ||      Product    y
CustomerA         ||      Product    z
CustomerB         ||      Product    i  
CustomerB         ||      Product    j

I want to get result like below...

CustomerName      ||      Product

CustomerA         ||      Product    X
"No Name"         ||      Product    y
"No Name"         ||      Product    z
CustomerB         ||      Product    i  
"No Name"         ||      Product    j

please suggest, what will be the best to achieve the above result...

Thanks Aman

Upvotes: 1

Views: 73

Answers (1)

lc.
lc.

Reputation: 116528

If you want or need to do this as a query, you can use the ROW_NUMBER() function to determine which is and isn't the "first" product per customer. Here I've defined "first" to mean having the lexically smallest Product name (fiddle):

;WITH CustomerProducts AS
(
    SELECT CustomerName, Product, 
        ROW_NUMBER() OVER(PARTITION BY CustomerName ORDER BY Product) rn
    FROM Table1
)
SELECT CASE WHEN rn=1 THEN CustomerName ELSE '"Same Customer"' END CustomerName,
    Product
FROM CustomerProducts
ORDER BY CustomerProducts.CustomerName, Product

Upvotes: 3

Related Questions