CODEWITHSUNDEEP
sql-server
mz1815
mz1815

Reputation: 35

How to Rename Duplicate Values in SQL Server

I have two columns that I am trying to bring back and the first column (ID) has multiple associated names to each ID. The values live in the same table.

I don't want to update the actual tables, and am trying to figure out how to Add/Modify the name of the duplicate records.

In theory my query would end up having two columns:

ID                 Name
--------------------------
1                  A
2                  B
3                  C1
3                  C2
3                  C3

Is there a way to add a value to the duplicate values just on extraction and not update the actual tables?

Upvotes: 0

Views: 693

Answers (2)

zambonee
zambonee

Reputation: 1647

Sounds like you are looking for a VIEW, like this:

CREATE VIEW dbo.vPartitioned AS
SELECT 
    ID, 
    char(64 + ID) + 
        CASE 
        WHEN COUNT(*) OVER (PARTITION BY ID) > 1 THEN CONVERT(nvarchar, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)) 
        ELSE '' 
        END Name 
FROM [table]

Then, select from the view instead of the table. Be warned though, the order of Name is arbitrary and may not be the same each time you select from the view.

Upvotes: 0

Chris Mack
Chris Mack

Reputation: 5208

Something like this would do it:

SELECT
    ID
    ,
        Name +
        CASE
            WHEN COUNT(*) OVER (PARTITION BY ID) = 1
            THEN ''
            ELSE CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS varchar)
        END Name
FROM YourTable

Upvotes: 1

Related Questions