Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Issue in how to pivot!

    Hello all,

    Please see my attached file for my data layout in TSQL query. I am not sure how to convert several rows into columns as my detailed format. I heard the Pivot technique but I could get them in my issue.
    This is my data t-sql query result into tempTable
    SchoolId | Firstname | Lastname |Class | Subject | Score
    --------------------------------------------------------------------
    1 John Smith 6 Math 85
    1 John Smith 6 Science 100
    1 John Smith 6 English 85
    2 Julie Chan 7 Geography 75
    2 Julie Chan 7 Math 100
    3 Sara West 8 Science 85
    3 Sara West 8 English 90
    ...

    I would like to covert my above data into the following order:

    SchoolID | Firstname | Lastname | Class | Math | Science | English | Geography | etc…
    ------------------------------------------------------------------------------------------------
    1 John Smith 6 85 100 85 NA
    2 Julie Chan 7 100 NA NA 75
    3 Sara West 8 NA 85 90 NA
    ...

    Any expertise in TSQL query to help me out? I really appreciate your time. Thanks in advance.
    Attached Files Attached Files
    Last edited by avt2k6; 03-31-13 at 23:53.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This is really an issue for your front-end/reporting layer.

    However if you feel that you must do this in SQL-land then you must pre-define all of your columns ahead of time (i.e. this won't dynamically get wider if new subjects are added; you'll have to modify the code).

    Code:
    SELECT schoolid
         , firstname
         , lastname
         , class
         , Max(CASE WHEN subject = 'Math'    THEN score END) As math
         , Max(CASE WHEN subject = 'Science' THEN score END) As science
         /* etc */
    FROM   your_table
    GROUP
        BY schoolid
         , firstname
         , lastname
         , class
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Thanks for all. I used PIVOT and got what I want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •