how to select 2 table like this

Question

How to select table with condition like this.

I have a table that holds all kinds of subjects like this.

table subjects

|===========|============|
|Subjects id|  Subjects  |
|===========|============|
|     01    |mathematics |      
|     02    |biology     |     
|     03    |geography   |       
|     04    |physics     |      
|===========|============|

then I also have a table to hold the value of each student like this.

table score

|==========|============|===============|
|Student id|Subjects id |     Score     |
|==========|============|===============|
|  10001   |     01     |       8       |
|  10001   |     02     |       6       |
|  10001   |     03     |       7       |
|  10001   |     04     |       9       |
|  10002   |     01     |       5       |
|  10002   |     02     |       7       |
|  10002   |     03     |       10      |
|  10002   |     04     |       7       |
|  10003   |     01     |       6       |
|  10003   |     02     |       7       |
|  10003   |     03     |       8       |
|  10003   |     04     |       9       |
|==========|============|===============|

I want to create a query with a form like the following table but i dont know how to make it.

|==========|=============|=========|===========|=========|
|Student id| mathematics | biology | geography | physics |
|==========|=============|=========|===========|=========|
|  10001   |     8       |    6    |     7     |    9    |
|  10002   |     5       |    7    |    10     |    7    |
|  10003   |     6       |    7    |     8     |    9    |
|==========|=============|=========|===========|=========|

please help me to solve this problem. Sorry my english is bad. I am still beginner

`pivot` is what you're looking for, likely a dynamic pivot. also, it's usually either [tag:mysql] or [tag:sql-server], not both. — Kritner, Dec 06 '16 at 19:50
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Kritner, Dec 06 '16 at 19:51

score 1 · Answer 1 · answered Dec 06 '16 at 19:53

This should work, assuming there is no duplicate in the score table, and those are the only 4 subjects you have. Or you can use PIVOT too.

SELECT
    sub.[Student Id]
    , CASE sc.[Subjects ID]
        WHEN '01' THEN sc.Score
        ELSE NULL
    AS mathematics
    , CASE sc.[Subjects ID]
        WHEN '02' THEN sc.Score
        ELSE NULL
    AS biology
    , CASE sc.[Subjects ID]
        WHEN '03' THEN sc.Score
        ELSE NULL
    AS geography
    , CASE sc.[Subjects ID]
        WHEN '04' THEN sc.Score
        ELSE NULL
    AS physics
FROM
    subjects sub 
    JOIN score sc ON sub.[Subjects ID] = sc.[Subjects ID]

score 1 · Accepted Answer · answered Dec 06 '16 at 19:57

1

You have some ways to do that, but trying not to create temporary tables, you can do something like that:

select
    s.id,
    avg(case when sb.id = '01' then s.score end) as math,
    avg(case when sb.id = '02' then s.score end) as bio

from student s
join subject sb on (sb.id = s.subject_id)

group by s.id

Just fill the sum/case lines to the other subjects as you need!

Hope it helps.

answered Dec 06 '16 at 19:57

jfneis

2,079
18
31

many sql platforms will give a warning here since null values will be ignored. – Hogan Dec 06 '16 at 20:02
@Hogan yeap, you're right! But that's exactly what we want in this case, right? :) – jfneis Dec 06 '16 at 20:03
well not if a person wants to live a life without warnings – Hogan Dec 06 '16 at 20:05
sorry, i am getting error in 'State 1, Line 3 Operand data type varchar is invalid for avg operator.' – swatzz Dec 06 '16 at 20:33
@swatzz that's because I used a numeric data type and your score is varchar (why?). simply cast it as a double inside the cast. – jfneis Dec 07 '16 at 11:01
oh, okay. i change the avg to max and success. Thank You. – swatzz Dec 07 '16 at 19:15
@swatzz yes, that's another solution, as far as you have only 1 score for subject! Glad it helped! Tks for the answer mark! – jfneis Dec 07 '16 at 21:21

score 0 · Answer 3 · answered Dec 06 '16 at 20:09

You can use a group by like the other answers or left joins like this:

select
  students.id, 
  mat.score as mathematics,
  bio.score as biology,
  geo.score as geography,
  phy.score as physics
from (
  SELECT DISTINCT studentid as id
  from score
) as students
left join score as mat on mat.studentid = students.id and mat.subjectid = 1
left join score as bio on bio.studentid = students.id and bio.subjectid = 2
left join score as geo on geo.studentid = students.id and geo.subjectid = 3
left join score as phy on phy.studentid = students.id and phy.subjectid = 4

score 0 · Answer 4 · answered Jan 13 '17 at 08:26

You can use:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Subjects) 
                    from subjects
                    group by Subjects
                    order by Subjects
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Student_id,' + @cols + ' 
            from 
             (
                select      D2.Student_id, D1.Subjects, D2.Score
                From        subjects D1
                Inner Join  score D2 
                    On      D1.Subjects_id = D2.Subjects_id
            ) x
            pivot 
            (
                sum(Score)
                for Subjects in (' + @cols + ')
            ) p '

execute(@query);

how to select 2 table like this

4 Answers4

Linked