1 2 3 4 5 6 7 8 9
| SELECT U.ID, SUBSTRING(U.FirstName,1,1) + SUBSTRING(U.LastName,1,1) AS Initiale,
CASE COUNT(UU.ID)
WHEN 0 THEN SUBSTRING(U.FirstName,1,1) + SUBSTRING(U.LastName,1,1)
ELSE SUBSTRING(U.FirstName,1,1) + SUBSTRING(U.LastName,1,1) + CAST(COUNT(UU.ID) AS VARCHAR(32))
END AS New_Name
FROM #user AS U
LEFT OUTER JOIN #user AS UU
ON U.ID > UU.ID AND SUBSTRING(U.FirstName,1,1) + SUBSTRING(U.LastName,1,1) = SUBSTRING(UU.FirstName,1,1) + SUBSTRING(UU.LastName,1,1)
GROUP BY U.ID, SUBSTRING(U.FirstName,1,1) + SUBSTRING(U.LastName,1,1) |
Partager