I have two columns in my table call

[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

 

[jjonesal]

You could use nested iif's to pull the data you need and concatenate together w/ &.

SELECT Left([TshirtSize],1) &
IIf([TshirtSize] Like "* S","1",IIf ([TshirtSize] Like "* M","2",IIf([TshirtSize] Like "* L","3",IIf([TshirtSize] Like "* XL","4",IIf([TshirtSize] Like "* 2XL","5",IIf([TshirtSize] Like "* 3XL","6","&quot)))))
& IIf([TshirtColor] Like "* Blue*","B","&quot AS comb
FROM Shirts;