Row data assignment to multiple local variable in single select statement

I have a table like below and
I want to assign all CDvalue column value to individual local variable in single query.

Like @Label, @MFG, @ORS, @DS
Table1
CDID CDCode CDValue
1 Label BO
1 MFG FOU
1 ORS OO1
1 DS DG
I tried using CASE statement but i think could not built the correct logic.
Please let me know if any other idea to get this done.
Thanks in advance
Thanks

All this to add these values in final query. Final query has some columns and need to display these additional values as well. I know it is looking quite uncommon.
I have other options to add different inline select statement from this table and display... the problem is one CDID has 20 different Code and values and i have to display all in my final query. I think it is not a good idea to fire 20 select statements and that too in select inline query.
Hope i am making sense here.
Thanks

Perhaps a PIVOT query would help?

Just i got two ideas to handle this scenario.
1. Insert the data into a table variable and then make those odd 20 statements (inline query). I guess it will give some advantage as firing a query 20 times against a table variable (which is in memory) will be better than firing query 20 times against physical table. This table variable has only 20 records, so there will not be any memory issue as well.
2. Fire 20 odd select statements and assign it to local variables and then use those variables in final select statement. I guess inline select statements are not good options. I am not sure but i remember i read somewhere. I'll try to investigate this from execution plan.
OR
Combination of above two. means use table variable and then assign it local variable and then use in final select statement. But i guess 20 odd local variables will eat some memory.
Am i making any sense here?
Thanks

PIVOT can turn the values from the second column into column names of your resultset, with the values from the third column as the column value.

[quote user="Adriaan"]
PIVOT can turn the values from the second column into column names of your resultset, with the values from the third column as the column value.
[/quote]
I tried it and could not make it because of little knowledge. I tried to search some example and could not find any without aggregate functions.
Could you please give me some hint on it?
Thanks

Hm - I got stuck with PIVOT in T-SQL too, where I could only get the column names to show up as the values too. Not the nicest of syntaxes!--NOT working as intended!
SELECT 'Label' AS Label, 'MFG' AS MFG, 'ORS' AS ORS, 'DS' AS DS
FROM (SELECT t.CDID, t.CDCode, t.CDValue FROM Table1 t) c
PIVOT (MAX(CDValue) FOR CDCode IN ([Label], [MFG], [ORS], [DS])) pvt
In Access, life is so much simpler:
TRANSFORM Min(Table1.CDValue)
SELECT Table1.CDID
FROM Table1
WHERE Table1.CDCode IN ('Label', 'MFG', 'ORS', 'DS')
GROUP BY Table1.CDID
PIVOT Table1.CDCode;

This simpler version of the syntax seems to work - please note that this is not from Books Online, I just made it up as I went along:SELECT
CDID, [Label], [MFG], [ORS], [DS]
FROM Table1
PIVOT (MAX(CDValue) FOR CDCode IN ([Label], [MFG], [ORS], [DS])) pvt

[quote user="Adriaan"]
This simpler version of the syntax seems to work - please note that this is not from Books Online, I just made it up as I went along:SELECT CDID, [Label], [MFG], [ORS], [DS]
FROM Table1
PIVOT (MAX(CDValue) FOR CDCode IN ([Label], [MFG], [ORS], [DS])) pvt
[/quote]
I am delighted to see this. Thanks a ton.
I guess now i can improve something against those 20 odd inline select statements.
Thanks again

Somehow I would be hesitant to put this syntax into production, simply because this is not how Books Online says it should be used.
Now if someone could post how the "proper" syntax from Books Online should be applied ...

[quote user="Adriaan"]
Somehow I would be hesitant to put this syntax into production, simply because this is not how Books Online says it should be used.
Now if someone could post how the "proper" syntax from Books Online should be applied ...
[/quote]
Now i am wondering if i should go by PIVOT approach or not in production. Another point, i am not sure if i would get any performance benifit or not.
I'll have to check this on staging on real data.

But to go by general, PIVOT is better or those 20 odd inline select statements. OR my ealier two approaches (first get the data in table variable and then fire 20 select statements against table variable in final query.
I am sure you could help me in this.
Thanks

Adriaan,
Where exactly you are feeling that this is not as per BOL?
I checked in BOL and could not find any deviation.
If would be of great help if you could assist me in this.
Many Thanks

Hm, well you're right - the BOL example was perhaps a little too complex (using a derived table, not explicitly saying you simply need to spell out the cross-tab values that you need as the column names) but it doesn't rule out my shorter version.

I came across one more option......
and the option is populate table variable and use with left outer join.................but i am bit apprehensive about 20 odd left outer joins and in from the SP (which i am referring) has used 42 odd left outer joins........................too much
I am sure separate 42 execution and assigning to local variable will be better option.
moreover experts say too much on using left outer join.......and somewhere i read that virtually LEFT OUTER JOIN usage is rare and should try to avoid
One more thing that i checked the exection plan with left outer join option and it is really huge.....and i guess to prepare that kind of plan will itself take so much internal resources.
what do you say?
I think i am asking too much.
Thanks

First off, there's no reason why a "huge" execution plan cannot be very efficient. Chances are it isn't, but if you're only ever processing rows for one CDID value there's a definite possibility that you can't do much optimization, and it becomes more a question of having a procedure that you can understand afterwards.
AFAIK you can use the PIVOT query as a derived table, so you could join on that. Not sure why you're hung up on outer joins - if you need one, you need one.