Pivot question

sql server United States
  • 11 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

  • 10 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 

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“I invented the term Object-Oriented, and I can tell you I did not have C++ in mind.” - Alan Kay