TSQL distinct list, but with extra data if a single original item-CodePudding

Ideally I'd like SQL2008 compliant answers, due to customer constraints

I need a distinct list of items, but with a column that returns a specific piece of data if the original data only had a single item in the first place...

Example data...

Id   Code
1    A
2    A
3    B

In the above example data I want to return a distinct list of Code, but if only a single instance of that code exists I want the Id to be returned (otherwise null)

Expected output...

Code  Id
A     null
B     3

I've got the following, but it feels like there should be an easier way of doing it, maybe using GROUP BY...

; WITH Data AS (
    SELECT Code, Id ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Id) AS RowNum
    FROM MyTable
)
SELECT D1.Code, CASE (SELECT COUNT(*) FROM Data D2 WHERE D2.Code = D1.Code) WHEN 1 THEN D1.Id ELSE NULL END
FROM Data D1
WHERE D1.RowNum = 1

Can this be done in a better way?

CodePudding user response：

Aggregation comes to mind here:

SELECT Code, CASE WHEN COUNT(*) = 1 THEN MAX(Id) END AS Id
FROM MyTable
GROUP BY Code;