SQL Puzzle | The Number Duplicate Puzzle

Puzzle Statement

This puzzle is simple. In this puzzle you have to add a number to duplicate values present in the table. E.g. if the values are SQL,SQL then the output should be SQL1, SQL2

Sample Input

1	Ramesh
2	Pawan
3	Pawan
4	Sharlee
5	Isha

Expected Output

1	Ramesh
2	Pawan1
3	Pawan2
4	Sharlee
5	Isha

Rules/RestrictionsThe solution should be should use “SELECT” statement or “CTE”.

ScriptUse below script to create table and insert sample data into it.

--

CREATE TABLE NumberDuplicates
(
	 ID SMALLINT
	,NAME VARCHAR(10)
)
GO

INSERT INTO NumberDuplicates
VALUES
(1,'Ramesh'),
(2,'Pawan'),
(3,'Pawan'),
(4,'Sharlee'),
(5,'Isha')

GO

--

SOLUTION 1 | Using Ranking Functions

--

SELECT Id,CASE WHEN cnt>1 THEN CONCAT(Name,rnk) ELSE NAME END Name FROM 
(
	SELECT * 
		, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) rnk
		, COUNT(*) OVER (PARTITION BY NAME) cnt
	FROM NumberDuplicates
)a
ORDER BY ID
--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com