--Create Table MarkMaster

Create Table MarkMaster
(mmId int,
 subject varchar(30) )


--Insert Sample Data Into MarkMaster(Removed data for student abc just to make it a little easy)
 
Insert Into MarkMaster
Select 1, 'Maths'
Union ALL
Select 2, 'phyicis'
Union ALL
Select 3, 'Chemistry'
Union ALL
Select 4, 'Enlish'
Union ALL
Select 5, 'CS'


--Create Table MarkSub

Create Table MarkSub
(mmId int,
 student varchar(10),
 marksObtained int )


--Insert Sample Data Into MarkSub

Insert Into MarkSub
Select 1, 'abc', 75
Union ALL
Select 1, 'xyz', 70
Union ALL
Select 1, 'pqr', 75
Union ALL
Select 1, 'dfh', 90
Union ALL
Select 2, 'xyz', 75
Union ALL
Select 2, 'pqr', 80 
Union ALL
Select 2, 'dfh', 45
Union ALL
Select 3, 'xyz', 79
Union ALL
Select 3, 'pqr', 87 
Union ALL
Select 3, 'dfh', 95
Union ALL
Select 4, 'xyz', 89
Union ALL
Select 4, 'pqr', 47 
Union ALL
Select 4, 'dfh', 65
Union ALL
Select 5, 'abc', 85
Union ALL
Select 5, 'xyz', 49
Union ALL
Select 5, 'pqr', 57 
Union ALL
Select 5, 'dfh', 65


--Required Query

Select p.student, p.Maths, p.phyicis, p.Chemistry, p.Enlish, CS From
(Select a.student, b.subject, a.marksObtained From MarkSub As a
JOIN MarkMaster as b ON a.mmId = b.mmId) As t
Pivot
(MAX(marksObtained) For Subject In (Maths, phyicis, Chemistry, Enlish, CS)) as p

quote:

---

Originally posted by sheriefes

Thanks Vinu

God Bless u

---