SQLTeam.com | Weblogs | Forums

Query With Unique Value

So I have a table: Stuff
Keys are : Happ, RecordID, KeyID

Rows
Happ: RecordID: KeyID: Description
ABC R1234 C001 Mike
ABC R1234 Default Default for All
ABC R1235 Default Default test
ABC R1236 Default Default Guess
ABC R1237 C0001 Mikes Bio
ABC R1237 Default default Bio

What I want is:
All Records with unique RecordID for KeyID='C0001'
and then those records where there was NOT a unique value for C0001,
returned back 1 one record set

So at the end of the query, in one recordset, the following would be returned
ABC R1234 C001 Mike
ABC R1235 Default Default test
ABC R1236 Default Default Guess
ABC R1237 C0001 Mikes Bio

My attempts of doing a Select have all failed.

Thanks

Don't have any directly usable data to test my query with, but this should at least be very close:

;WITH cte_stuff AS (
    SELECT 
        MAX(CASE WHEN KeyID = 'C001' THEN Happ END) AS C001_Happ,
        MAX(CASE WHEN KeyID <> 'C001' THEN Happ END) AS Happ,
        RecordID, 
        MAX(CASE WHEN KeyID = 'C001' THEN KeyID END) AS C001,
        MAX(CASE WHEN KeyID <> 'C001' THEN KeyID END) AS KeyID,
        MAX(CASE WHEN KeyID = 'C001' THEN Description END) AS C001_Description,
        MAX(CASE WHEN KeyID <> 'C001' THEN Description END) AS Description
    FROM stuff
    GROUP BY RecordID
)
SELECT
    COALESCE(C001_Happ, Happ) AS Happ,
    RecordID,
    COALESCE(C001, KeyID) AS KeyID,
    COALESCE(C001_Description, Description) AS Description
FROM cte_stuff
ORDER BY RecordID
1 Like