Community discussion forum

Pivot question

• • Reply

  6 months ago

  by pavelbk

  pavelbk

  , United States

  Joined 2 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
• Advertisement

  Simply the fastest line-level profiler for .NET ever

  “The low overhead means it has minimal impact on the execution of my program”
  Mark Everest, Development Team Leader, Renault F1 Team Ltd.

  Try out the new ANTS Profiler 4 for yourself. Download your 14-day trial now

• • Reply

  6 months ago

  by joe90

  J Alan

  , United Kingdom

  Joined 3 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