Pivot question

• • Reply

  12 years ago

  by pavelbk

  pavelbk

  , United States

  Joined 15 years ago

  Hi all,         

  I have a table which contain Course and student.

  eg: Student    Course      

        A            Arts          

        B           Maths         

        C           Maths        

        D           Arts            

  I need to write a query which returns the follwing data

  Course          Student

  Maths          B        C

  Arts             A        D

  I have heard in SQL 2005 PIVOT is available. But I was not able to figure out a better way to fulfill my requirement.

  Please help me if anybody have any idea.

  Thank you very much in advance

  Report abuse
• • Reply

  12 years ago

  by joe90

  J Alan

  , United Kingdom

  Joined 15 years ago

   Perhaps not what you want, and maybe a little inefficient, but you could consider:

   create function ufn_course_students
  (
      @course nvarchar(50)
  )
  returns nvarchar(100)
  as
  begin
      declare @student nvarchar(100)
  
      set @student = ''
  
      select
          @student = @student + student + ' '
      from studentcourse2
      where course = @course
  
  
      return @student
  end
  
  
  select distinct
      course,
      dbo.ufn_course_students(course)
  from studentcourse2
  order by course desc
  

  
  which will get you what you want.

  Not used the "pivot" command before .. but I'm not sure it'll get you what you want. Perhaps someone with more experience than myself can chip in here.

  Joe 

  Report abuse