sql - Displaying data present in multiple columns

Question

need a little help. I am working on SQL server and I need to display data that is in multiple columns but different rows. I tried using a exists but that did not work. And join does not work in this scenario and neither does and/or statement.

Part name      supplier01     supplier02     supplier03
a1                 1              3            zzz 
b1                 2              4
c1                 3             zzz
d1                 4                            1
e1                zzz             1             2

Now in the example above, part a is sold by supplier 1, 3 and 5. Part b is sold by supplier 2 and 4 so on and so forth.

What I need to do is display a list of all the parts sold by supplier one but in different columns. So the display should look like

Part name      supplier01     supplier02     supplier03
a1                 1                                    
d1                                               1
e1                                 1             

Any help or thoughts are appreciated. Update: Sorry in my initial question I forgot to mention that Partname, supplier01, supplier02, supplier 03 columns are navchar.

Answer 1

I wonder if changing the format of the result set would actually be more helpful:

select t.partname, v.*
from t cross apply
     (values (supplier01, 1), (supplier02, 2), (supplier03, 3)) v(supplier, which)
where supplier = 1;

This returns the values in separate rows, with the "column" as a number. This seems like a more helpful way to represent the information.