sql - How to replace union all in my TSQL -

i'm getting data file below format. have table stored item description.

i looking ouput categoryid , itemcode , item description. since data items in same row distingushed category it's getting little tricky.

categoryid  name    itemcode1   itemcode2   itemcode3   itemcode4   itemcode5    1        test1   1234578     87ba1234                 err12345    2        test2   baaa9aaj                143paam 

itemdescription stored below:

itemcode     itemdesc 12345678     item1 87ba1234     item2 , on 

i'm able output want selecting each item , description separately , bind them union all. feel there should better way it. appreciate guidance.

edit: t- sql union all

select categoryid, isnull(item.itemdescription, 'unknown') itemdesc                          (                 select categoryid, itemcode1 code                 itemdesc                 union       --aaae9aaf                 select  categoryid, itemcode2 code                 itemdesc                 union       --aaag9aaf                 select  categoryid, itemcode3 code                 itemdesc                 union                        select categoryid, itemcode4 code                 itemdesc                 union                        select  categoryid, itemcode5 code                 itemdesc             )tc             left join @itemdescription item on item.itemcode = tc.code 

if understand right, want unpivot data.

try using cross apply:

select     t.categoryid,     x.itemcode,     d.itemdesc your_table t cross apply (     values (t.itemcode1),(t.itemcode2),(t.itemcode3),            (t.itemcode4),(t.itemcode5)     ) x (itemcode) join itemdescription d on x.itemcode = d.itemcode;