SELECT APP,COLOUR,FLAVOUR + CHAR(64 + ROW_NUMBER() OVER (PARTITION BY FLAVOUR ORDER BY APP)) AS Seq
FROM 
(
SELECT APP,COLOUR,
CASE WHEN SUM(CASE WHEN COLOUR = 'RED' THEN 1 END) OVER (PARTITION BY APP) > 0 THEN 'RED'
     WHEN SUM(CASE WHEN COLOUR = 'YELLOW' THEN 1 END) OVER (PARTITION BY APP) > 0 THEN 'YELLOW'
     ELSE 'GREEN'
END AS FLAVOUR
FROM Table
)t

quote:

---

Originally posted by waterduck

;WITH TableA AS(
	SELECT	*, CASE WHEN COLOUR IN ('GREEEN', 'GREEN') THEN 3
				WHEN COLOUR = 'RED' THEN 1
				WHEN COLOUR = 'YELLOW' THEN 2 END Priority
	FROM	(VALUES('ap1','GREEEN'), 
					('ap1','YELLOW'), 
					('ap1','YELLOW'), 
					('ap1','GREEEN'),
					('ap1','GREEEN'),
					('ap2','YELLOw'),
					('ap2','YELLOW'),
					('ap2','YELLOW'),
					('ap3','GREEEN'),
					('ap3','YELLOW'),
					('ap3','RED'),
					('ap3','GREEEN'),
					('ap4','GREEN'),
					('ap4','GREEN'),
					('ap4','GREEN')) AS TableA(APP, COLOUR)
)
SELECT	*
FROM	TableA TableA
CROSS APPLY
(SELECT TOP 1 COLOUR + CHAR(64 + DENSE_RANK() OVER (PARTITION BY Priority ORDER BY APP))
FROM TableA TableB
WHERE TableA.APP = TableB.APP
ORDER BY Priority)TableB(FLAVOUR)
ORDER BY 1,2

---