Need help sql query for report?

Question

I've got report table as below : Report(StudentName, SubJectName, Mark) and example data below

StudentName    |    SubjectName   |  Mark
Tommy               A                6
Tommy               B                8
Tommy               C                10
Susan               A                7
Susan               B                10

So I'd like to query and display As below:

SubjectName  | A | B |  C
Tommy          6   8    10
Susan          7   10   0

Could you suggest me how to do this?. I researched using pivot but I don't know how to apply?

Thanks in advance!

Answer 1

I've used pivot SQL and my problem was solved. Below is my query

SELECT StudentName, A, B, ISNULL(C,0) as C
FROM (select StudentName, subjectName, Mark from dbo.Student) as src
Pivot(max(mark) for subjectName in(A, B, C))  as pvt

and also below is more example about pivot http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Fiddle

Answer 2

You need to use a pivot,

FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO

See example : SQL Pivot

Answer 3

Use case statement

select studentname,
max(case when subjectname='A' then mark else 0 end) 'A',
max(case when subjectname='B' then mark else 0 end) 'B',
max(case when subjectname='C' then mark else 0 end) 'C'
from table1
group by studentname

fiddle