loop through duplicate records and append an id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

loop through duplicate records and append an id

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2015-02-03 : 12:54:44

Hi Friends,

I have a table called Customer with following fields:

CustomerID Fname LName

I want to selct Fname+LNAme which are having more than 1 records and add a new field FullName where a alphabet is appended to their Names

1 John Hopkins

3 John Hopkins

6 John Hopkins
Then I want to show like this

1 john Hopkins JohnHokins
3 john hopkins JohnHopkins(b)
6 john Hopkins JohnHopkins(c)

How can i achieve it ..Thank You In advance

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-03 : 14:29:31

[code]
SELECT CustomerID, FName, LName,
FName + LName + CASE WHEN row_num = 1 THEN '' ELSE '(' + CHAR(96 + row_num) + ')' END AS FullName
FROM (
SELECT CustomerID, FName, LName, ROW_NUMBER() OVER(PARTITION BY FName, LName ORDER BY CustomerID) AS row_num
FROM (
SELECT 1 AS CustomerID, 'John' AS FName, 'Hopkins' AS LName UNION ALL
SELECT 3 AS CustomerID, 'John' AS FName, 'Hopkins' AS LName UNION ALL
SELECT 6 AS CustomerID, 'John' AS FName, 'Hopkins' AS LName
) AS test_data
) AS derived

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources