Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    2

    Thumbs up Unanswered: Query Help plaease

    Hi, I'm new for here and new for mysql too.

    so want to get query for crosstab some fields as columns.

    As a example

    let assume I has simple Student Table like follows

    +----------+---------+------------+
    --stu_id--||-subject-||-stu_marks-|
    +----------+---------+------------+
    --1-----|----Word----|----50----|
    --2-----|----Excel----|----75----|
    --1-----|----Excel----|----80----|
    --2-----|---Access---|----68----|
    --2-----|----Word----|----33----|


    so I want query to alter above table as following

    +----------+---------+------------+----------+
    |--stu_id---|--Word--|----Excel----|--Access--|
    +----------+---------+------------+----------+
    |-----1----|----50---|-----80-----|-----------|
    |-----2----|----33 --|-----75-----|----68-----|
    +----------+---------+------------+----------+

    If some one can help me to get exact query its great help

    Thanks
    Last edited by sanjeeme; 05-14-11 at 08:29.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT stu_id
         , MAX(CASE WHEN subject = 'Word'
                    THEN stu_marks
                    ELSE NULL END) AS 'Word'
         , MAX(CASE WHEN subject = 'Excel'
                    THEN stu_marks
                    ELSE NULL END) AS 'Excel'
         , MAX(CASE WHEN subject = 'Access'
                    THEN stu_marks
                    ELSE NULL END) AS 'Access'
      FROM student
    GROUP
        BY stu_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    2

    Thumbs up Thanksss

    Thanksssss friend. its really help for my other task.

  4. #4
    Join Date
    Apr 2011
    Posts
    19

    result

    see image for result
    Attached Thumbnails Attached Thumbnails 2011-05-19_114533.jpg  

Posting Permissions

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