Displaying multiple columns from a single column select statement

I'm trying to extract data from 开发者_开发技巧a table and display multiple columns based on the results from a single column.

For example, my table has a recordId, a valueId and a value. It can look something like this

recordId   |  valueId   |  value
1          |   5        |  2011-03-24
2          |   5        |  2011-03-25
3          |   3        |  Bobcat
4          |   3        |  Backloader
5          |   4        |  Mono
6          |   4        |  Stereo

I'm trying to make multiple columns based on the valueId (e.g. valueId 5 is join date, valueId 3 is vehicle type, while valueId 4 is speaker type) but whenever I try to create their own columns, I end up getting the same data in both of the column returns.

e.g. I'll use something like

select recordId as Records, valueId as [Join Date], valueId as [Vehicle type], valueId as [Speaker Type], value as [Data Entered] where valueId = 5 OR valueId = 3 OR valueId = 4

This will create the proper header, but I will get the same data in each column for each value call (e.g. the first row will have 2011-03-24 under Join Date, Vehicle Type and Speaker Type, row 2 will be 2011-03-25 instead and row 3 will be Bobcat)

Thanks for any help provided!

Edit : Thanks to everyone that provided help! I'd upvote if I could but whenever I try it tells me I have to log in or register (Which is weird since I am logged in to edit and approve answers...)

Without using Dynamic SQL you can PIVOT/ROTATE the data using the following technique. Although it has the drawback that you have to hard code all the values.

select recordId as Records, 
  case when valueid=5 then value else null end [Join Date], 
  case when valueid=3 then value else null end [Vehicle type], 
  case when valueId=4 then value else null end [Speaker Type]
 from Table
where valueId in (5,3,4)

   RecordID   |   Join Date   |   Vehicle Type    | Speaker Type
   1          |   2011-03-24  |   null            | null
   2          |   2011-03-25  |   null            | null
   3          |   null        |   Bobcat          | null
   4          |   null        |   Backloader      | null
   etc....

select recordId as Records,
       case
         when valueId = 5 then value
         else null
       end as [Join Date],

       case
         when valueId = 3 then value
         else null
       end as [Vehicle type],

       case
         when valueId = 4 then value
         else null
       end as [Speaker Type]

  from yourTable

Something like this perhaps?

select recordId as Records, 
       case valueId when 5 then value end as [Join Date], 
       case valueId when 3 then value end as [Vehicle type], 
       case valueId when 4 then value end as [Speaker Type], 
       value as [Data Entered] 
from YourTable       
where valueId = 5 OR valueId = 3 OR valueId = 4

Result:

Records     Join Date  Vehicle type Speaker Type Data Entered
----------- ---------- ------------ ------------ ------------
1           2011-03-24 NULL         NULL         2011-03-24
2           2011-03-25 NULL         NULL         2011-03-25
3           NULL       Bobcat       NULL         Bobcat
4           NULL       Backloader   NULL         Backloader
5           NULL       NULL         Mono         Mono
6           NULL       NULL         Stereo       Stereo