combining sql statements?

[aarontra]

I have two columns in my table called tshirtsize and tshirtcolor, which I want to encode into a 3 character code.

[Sleeve][Size][color]
[S|L] [1-6] [B|G]

where
tshirtsize = 'Short Sleeve M'
tshirtcolor = 'Stonewashed Blue'

result should be:
S2B


I would like to do this in sql.
Here is what I have so far.
How do I combine these statements to encode the data in the table.

select tshirtsize, 'S' as sleevecode from ConferenceUsers where tshirtsize
like 'Short Sleeve%'

select tshirtsize, 'L' from ConferenceUsers where tshirtsize
like 'Long Sleeve%'

select tshirtsize, 1 from ConferenceUsers where tshirtsize
like '%S'

select tshirtsize, 2 from ConferenceUsers where tshirtsize
like '%M'

select tshirtsize, 3 from ConferenceUsers where tshirtsize
like '%L'

select tshirtsize, 4 from ConferenceUsers where tshirtsize
like '%XL'

select tshirtsize, 5 from ConferenceUsers where tshirtsize
like '%2XL'

select tshirtsize, 6 from ConferenceUsers where tshirtsize
like '%3XL'


select tshirtcolor,'B' from ConferenceUsers where tshirtcolor
like '%Blue'

select tshirtcolor, 'G' from ConferenceUsers where tshirtcolor
like '%Green'



Thank you
Aaron

 

[devprogrammer]

I would start by using a case statement when doing the select.

For example:
Select SleeveCode = case when tshirtsize like 'Short Sleeve%' then 'S'
when tshirtsize like 'Long Sleeve%' then 'L'
end,

tshirtSize = case when tshirtsize like '%S' then
'1'
when tshirtsize like '%M' then '2'
when tshirtsize like '%L' then '3'
when tshirtsize like '%XL' then '4'
when tshirtsize like '%2XL' then '5'
when tshirtsize like '%3XL' then '6'
end,

tshirtcolor = case when tshirtcolor like '%BLUE' then 'B'
when tshirtcolor like '%Green' then 'G'
end

from ConferenceUsers


The end result is you will have 3 fields with the desired codes.

Hope that helps,

Denise D.