Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34

    Unanswered: build query from output data

    We have one table Say ‘student’ with below structure:

    S_id int null,
    S_name varchar(100),
    S_marks int null

    Data is something like :

    s_id subject s_marks
    1 English 40
    1 Spanish 45
    1 German 50
    2 Physics 60
    2 Chemistry 65
    2 Maths 70
    3 Statistics 80
    3 Economics 85

    Write such a query so that I will get output as below

    s_id subject s_marks
    1 English 40
    1 Spanish 45
    1 German 50
    1 NA NA
    1 NA NA
    1 NA NA
    1 NA NA
    1 NA NA
    2 NA NA
    2 NA NA
    2 NA NA
    2 Physics 60
    2 Chemistry 65
    2 Maths 70
    2 NA NA
    2 NA NA
    3 NA NA
    3 NA NA
    3 NA NA
    3 NA NA
    3 NA NA
    3 NA NA
    3 Statistics 80
    3 Economics 85

    Thanks in advance...

  2. #2
    Join Date
    Aug 2003
    Posts
    43
    Did not get in single query. Have a look at these queries.

    CREATE TABLE student
    (
    sid INT,
    sname VARCHAR(20),
    smarks INT
    )

    INSERT student values(1, 'English', 40)
    INSERT student values(1 , 'Spanish', 45)
    INSERT student values(1, 'German',50)
    INSERT student values(2, 'Physics', 60)
    INSERT student values(2, 'Chemistry', 65)
    INSERT student values(2, 'Maths', 70)
    INSERT student values(3, 'Statistics', 80)
    INSERT student values(3, 'Economics', 85)

    SELECT DISTINCT sname INTO #subjects FROM student

    SELECT DISTINCT A.sid, B.sname INTO #allmarks
    FROM student A,
    #subjects B

    SELECT B.sid, CASE WHEN A.smarks IS NULL THEN "NA" ELSE B.sname END SubName, CASE WHEN A.smarks IS NULL THEN "NA" ELSE CONVERT(CHAR(10), A.smarks) END Marks
    FROM student A,
    #allmarks B
    WHERE A.sid=*B.sid
    AND A.sname=*B.sname
    ORDER BY 1

Posting Permissions

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