Replacing CASE in SQL query

Question

I got three columns with binary values. Then i got a query which makes a string based on those values. I made this work using case. But it's quite huge (it's a part of a bigger query), and i was wondering maybe there is a better way of doing this?

SELECT (CASE
    WHEN TableA.flag1 = 1 AND TableA.flag2 = 1 AND TableA.flag3 = 1 THEN 'CGR'
    WHEN TableA.flag1 = 1 AND TableA.flag2 = 1 THEN 'CG'
    WHEN TableA.flag1 = 1 AND TableA.flag3 = 1 THEN 'CR'
    WHEN TableA.flag2 = 1 AND TableA.flag3 = 1 THEN 'GR'
    WHEN TableA.flag1 = 1 THEN 'C'
    WHEN TableA.flag2 = 1 THEN 'G'
    WHEN TableA.flag3 = 1 THEN 'R'
    ELSE 'nothing'
END)
FROM TableA

Im working on MSSQL 2000 server.

Answer 1

You can use left() instead.

select left('C', T.flag1)+
       left('G', T.flag2)+
       left('R', T.flag3)
from TableA as T

Answer 2

I don't know if this is "better" but it is more concise:

SELECT ((CASE WHEN TableA.flag1 = 1 THEN 'C' ELSE '' END) +
        (CASE WHEN TableA.flag2 = 1 THEN 'G' ELSE '' END) +
        (CASE WHEN TableA.flag3 = 1 THEN 'R' ELSE '' END)
       )
FROM TableA;

Okay, this isn't exactly the same because you get '' instead of 'nothing'. But I think the empty string does a better job of representing "no flags" than 'nothing' does.