how to convert rows to columns

how to convert rows to columns

• deys

  Nope, not without using dynamic SQL. It isn't too hard, you just build the SELECT clause dynamically based on the data. In this post there is an example: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=488720&SiteID=1
  
  

• HakanA

  Do you know if its possible to do this for a variable amount of Visits
  
  

• David Scrutton

  select patientno
  ,visit1 = max(case when visit = 1 then 1 else null end)
  ,visit1date = max(case when visit = 1 then experimentdate else null end)
  ,visit2 = max(case when visit = 2 then 2 else null end)
  ,visit2date = max(case when visit = 2 then experimentdate else null end)
  ,visit3 = max(case when visit = 3 then 3 else null end)
  ,visit3date = max(case when visit = 3 then experimentdate else null end)
  from <tablename>
  group by patientno
  
  
  
• how to convert rows to columns