Urgent Query

[rupalirane07]

I have on table Student

StudentName Subjects Marks

Ram Phys 80
Ram Chem 70
Ram Maths 50
Ramesh Phys 60
Ramesh Chem 78
Ramesh Maths 75

i want to disply result like this:

StudentName Physics Chemistry Maths
Ram 80 70 50
Ramesh 60 78 75

any help ........pls urgent

[azimmer]

I have on table Student

StudentName Subjects Marks

Ram Phys 80
Ram Chem 70
Ram Maths 50
Ramesh Phys 60
Ramesh Chem 78
Ramesh Maths 75

i want to disply result like this:

StudentName Physics Chemistry Maths
Ram 80 70 50
Ramesh 60 78 75

any help ........pls urgent

If you use SQL Server 2000, read this: http://www.mssqltips.com/tip.asp?tip=937

If you're luck enough to use 2005 there is a much simpler way: http://www.mssqltips.com/tip.asp?tip=1019

[itsmatta]

-------------------------------------**************----------------------------
USE MASTER;
GO

CREATE DATABASE STUDENTSUBMARKS;
GO

USE STUDENTSUBMARKS;

CREATE TABLE STUDENT
(
SLNO INT NOT NULL,
STUDENTNAME VARCHAR(50) NULL,
SUBJECTS VARCHAR(25) NULL,
SUBJECTMARKS BIGINT
);
GO

INSERT INTO STUDENT VALUES(1, 'RAM', 'PHYSICS', 80);
INSERT INTO STUDENT VALUES(1, 'RAM', 'CHEMISTRY', 70);
INSERT INTO STUDENT VALUES(1, 'RAM', 'MATHS', 50);
INSERT INTO STUDENT VALUES(2, 'RAMESH', 'PHYSICS', 60);
INSERT INTO STUDENT VALUES(2, 'RAMESH', 'CHEMISTRY', 78);
INSERT INTO STUDENT VALUES(2, 'RAMESH', 'MATHS', 75);

SELECT * FROM STUDENT
-------------------------------------**************----------------------------

1 RAM PHYSICS 80
1 RAM CHEMISTRY 70
1 RAM MATHS 50
2 RAMESH PHYSICS 60
2 RAMESH CHEMISTRY 78
2 RAMESH MATHS 75

------------------------------------**************----------------------------------
Hope this is useful to you and it will work.....

Happy Coding...

Regards,
Laksh

[rupalirane07]

i dont want to insert i want to get that in select query result

-------------------------------------**************----------------------------
USE MASTER;
GO

CREATE DATABASE STUDENTSUBMARKS;
GO

USE STUDENTSUBMARKS;

CREATE TABLE STUDENT
(
SLNO INT NOT NULL,
STUDENTNAME VARCHAR(50) NULL,
SUBJECTS VARCHAR(25) NULL,
SUBJECTMARKS BIGINT
);
GO

INSERT INTO STUDENT VALUES(1, 'RAM', 'PHYSICS', 80);
INSERT INTO STUDENT VALUES(1, 'RAM', 'CHEMISTRY', 70);
INSERT INTO STUDENT VALUES(1, 'RAM', 'MATHS', 50);
INSERT INTO STUDENT VALUES(2, 'RAMESH', 'PHYSICS', 60);
INSERT INTO STUDENT VALUES(2, 'RAMESH', 'CHEMISTRY', 78);
INSERT INTO STUDENT VALUES(2, 'RAMESH', 'MATHS', 75);

SELECT * FROM STUDENT
-------------------------------------**************----------------------------

1 RAM PHYSICS 80
1 RAM CHEMISTRY 70
1 RAM MATHS 50
2 RAMESH PHYSICS 60
2 RAMESH CHEMISTRY 78
2 RAMESH MATHS 75

------------------------------------**************----------------------------------
Hope this is useful to you and it will work.....

Happy Coding...

Regards,
Laksh

[rupalirane07]

SELECT * FROM student
--SELECT count(Studentname),studentname,
--subjects FROM Student GROUP BY Subjects,studentname





SELECT studentname,
CASE WHEN subjects='Phy' THEN marks END AS Physics,
CASE WHEN subjects='Chem' THEN marks END AS Chemistry,
CASE WHEN subjects='Maths' THEN marks END AS Maths

FROM Student

SELECT DISTINCT studentname from
(SELECT studentname,
CASE WHEN subjects='Phy' THEN marks END AS Physics,
CASE WHEN subjects='Chem' THEN marks END AS Chemistry,
CASE WHEN subjects='Maths' THEN marks END AS Maths

FROM Student)A
GROUP BY StudentName


SELECT DISTINCT studentname,sum(physics) AS Physics,sum(chemistry) AS Chemistry,sum(maths) AS Maths from
(SELECT studentname,
CASE WHEN subjects='Phy' THEN marks END AS Physics,
CASE WHEN subjects='Chem' THEN marks END AS Chemistry,
CASE WHEN subjects='Maths' THEN marks END AS Maths

FROM Student)A
GROUP BY StudentName
--,physics,chemistry,maths