Luis Cazares (4/29/2016)
Something like this?
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
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;