Be careful with the distinct here, can be very costly if the cardinality is high. Better do this in two parts.

😎

-- /*

IF OBJECT_ID(N'tempdb..#ServiceDesk') IS NOT NULL DROP TABLE #ServiceDesk;

Create table #ServiceDesk

(

Service_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,ServiceName varchar(20) NOT NULL

,Criteria varchar(20) NOT NULL

);

INSERT INTO #ServiceDesk (ServiceName,Criteria)

VALUES ('S1','ABC')

,('S1','ABC')

,('S1','XYZ')

,('S1','XYZ')

,('S1','PQR')

,('S1','PQR')

,('S1','PQR')

,('S3','ABC')

,('S2','MNO')

,('S2','MNO')

,('S2','MNO')

,('S2','MNO')

,('S4','MNO')

;

CREATE NONCLUSTERED INDEX TMP_NCLIDX_#SERVICEDESK_SERVICENAME_CRITERIA ON #ServiceDesk ( ServiceName ASC , Criteria ASC)

-- */

-- 80% of the cost of this query is the distinct sort

SELECT DISTINCT

CASE WHEN MAX(Criteria) OVER( PARTITION BY ServiceName) <> MIN(Criteria) OVER( PARTITION BY ServiceName)

THEN ServiceName + '-C-' + CAST(DENSE_RANK() OVER( PARTITION BY ServiceName ORDER BY Criteria) AS varchar(3))

ELSE ServiceName END AS ServiceName,

Criteria

FROM #ServiceDesk;

-- No sort operator needed for this query

;WITH BASE_DATA AS

(

SELECT

SD.ServiceName

,SD.Criteria

FROM #ServiceDesk SD

GROUP BY SD.ServiceName

,SD.Criteria

)

SELECT

CASE

WHEN COUNT(BD.ServiceName) OVER

(

PARTITION BY BD.ServiceName

) = 1 THEN BD.ServiceName

ELSE

CONCAT(BD.ServiceName,'-C-',CONVERT(VARCHAR(3),DENSE_RANK() OVER

(

PARTITION BY BD.ServiceName

ORDER BY BD.Criteria

),0))

END AS ServiceName

,BD.Criteria

FROM BASE_DATA BD;