Generate ordinal numbers in SQL Server

Hi Mehram,

Refer below query.

SQL

;WITH CTE
AS
(
    SELECT 1 num
    UNION ALL
    SELECT num + 1
    FROM CTE
    WHERE num < 40
)
SELECT CONCAT(CAST(num AS VARCHAR(10)),
       CASE
           WHEN num % 100 IN (11,12,13) THEN 'th'
           WHEN num % 10 = 1 THEN 'st'
           WHEN num % 10 = 2 THEN 'nd'
           WHEN num % 10 = 3 THEN 'rd'
           ELSE 'th'
       END) AS 'Ordinal'
FROM CTE

Output