CASE and Inserting Commas

I want to build a CASE statement in SQL to transform data. The SQL is based
on Race. I have the following columns...
RaceAA(American Indian or Alaska Native)
RaceNH(Native Hawaiian)
RaceA(Asian)
RaceW(White)
RaceB(Black)
There are 'X's in these columns designating the Race and more than one may
be 'X'ed.
I want to transform the 'X's into numbers with commas in between. For
Example, if Tiger Woods came into our DataBase, the RaceA would have an 'X'
and needs to be transposed to '2', RaceW would have an 'X' and needs to be
transformed to '5', and RaceB would have an 'X' and needs to be transposed t
o
'3'.
My result should look like...2,5,3
CASE WHEN RaceA = 'X' then '2,'
WHEN RaceW = 'X' then '5,'
WHEN RaceB = 'X' then '3,'
would give me 2,5,3,
My concern here is the trailing ",". Is there any way to CASE the comma if
multiple Race columns are checked and work in the commas appropriately?
Am I asking SQL questions in the right forum?> I have the following columns...
> RaceAA(American Indian or Alaska Native)
> RaceNH(Native Hawaiian)
> RaceA(Asian)
> RaceW(White)
> RaceB(Black)
Why? Why not have a column called "Race" and then provide values to that
column, such as 'AA','NH','A','W','B', etc.

> There are 'X's in these columns designating the Race and more than one may
> be 'X'ed.
Someone can be Asian and Black and White and American Indian?

> I want to transform the 'X's into numbers with commas in between. For
> Example, if Tiger Woods came into our DataBase, the RaceA would have an
> 'X'
> and needs to be transposed to '2', RaceW would have an 'X' and needs to be
> transformed to '5', and RaceB would have an 'X' and needs to be transposed
> to
> '3'.
Why do you want to store this race code mapping in your code? Wouldn't it
make more sense to use a numeric code *in the data* (and have a lookup
table)? Then you don't have to go read stored procedure code to figure out
what the heck kind of person a 3 is, and you don't have to worry about
making sure all queries that use this logic match.

> would give me 2,5,3,
> My concern here is the trailing ",". Is there any way to CASE the comma if
> multiple Race columns are checked and work in the commas appropriately?
Well, you could produce 2,5,3, in a subquery, and in the outer query, say:
SELECT RaceList = CASE RIGHT(List,1)
WHEN ',' THEN LEFT(List, LEN(List)-1)
ELSE List
END
FROM
(
SELECT List = '2,5,3,'
UNION
SELECT List = '4'
) x
Or, you could do something really novel, and inject the commas where they
belong, on the presentation tier.

> Am I asking SQL questions in the right forum?
Yes but you should look at alternative designs. This is far from optimal.|||CASE seems to Mutually Exclusive. Is there any way to continue the CASE
statement so it looks at all the race columns and builds the result column
accordingly. In other words, once it sees that the "White" column has an "X"
,
it goes to the end when I need it to look at ALL the race columns.
Any help you could provide me here would be GREATLY appreciated!
Thanks!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why? Why not have a column called "Race" and then provide values to that
> column, such as 'AA','NH','A','W','B', etc.
>
> Someone can be Asian and Black and White and American Indian?
>
> Why do you want to store this race code mapping in your code? Wouldn't it
> make more sense to use a numeric code *in the data* (and have a lookup
> table)? Then you don't have to go read stored procedure code to figure ou
t
> what the heck kind of person a 3 is, and you don't have to worry about
> making sure all queries that use this logic match.
>
> Well, you could produce 2,5,3, in a subquery, and in the outer query, say:
> SELECT RaceList = CASE RIGHT(List,1)
> WHEN ',' THEN LEFT(List, LEN(List)-1)
> ELSE List
> END
> FROM
> (
> SELECT List = '2,5,3,'
> UNION
> SELECT List = '4'
> ) x
> Or, you could do something really novel, and inject the commas where they
> belong, on the presentation tier.
>
> Yes but you should look at alternative designs. This is far from optimal.
>
>|||You can string the CASE expressions together:
CASE WHEN X = 1 THEN 'A' ELSE '' END+
CASE WHEN X = 2 THEN 'B' ELSE '' END+
CASE WHEN X = 3 THEN 'C' ELSE '' END+ ...
but I share Aaron's views about the weakness of the design you are
implying. In particular, it's a mistake to design tables to mirror a UI
- columns are not check boxes!
David Portas
SQL Server MVP
--|||or:
CASE WHEN col1 = 1 THEN 'A' ELSE '' END+
CASE WHEN col2 = 1 THEN 'B' ELSE '' END+
CASE WHEN col3 = 1 THEN 'C' ELSE '' END+ ...
David Portas
SQL Server MVP
--