Help is needed to solve the SQL query.

Question

2.50/5 (2 votes)

See more:

in the student table , the data is present in this form,

sID	sName	sub	marks
1	Savita	EC1	30
1	Savita	EC2	19
1	Savita	EC3	28
1	Savita	EC4	30
1	Savita	EC5	60
2	Vidya	EC1	90
2	Vidya	EC2	80
2	Vidya	EC3	85
2	Vidya	EC4	75
2	Vidya	EC5	99
3	Tanesh	EC1	75
3	Tanesh	EC2	80
3	Tanesh	EC3	85
3	Tanesh	EC4	28
3	Tanesh	EC5	86

i wants to fetch the records in the below form using an sql query.

Sname EC1   EC2	EC3	EC4	EC5	Total
Savita	30	19	28	30	60	167
Tanesh	75	80	85	28	86	354
Vidya	90	80	85	75	99	429

i used that SQL Query:

SQL

select fStudentname as Student_name, EC1 ,EC2,EC3,EC4,EC5,Total =([EC1]+[EC2]+[EC3]+[EC4]+[EC5]) from (select fstudentname ,FSubject ,Fmarks from Student) a PIVOT(sum(Fmarks) For fsubject in (EC1,EC2,EC3,EC4,EC5) )b

the query works fine but i dn want to use PIVOT i wants to use JOIN. IS that POSSIBLE?

What I have tried:

the SQL query i used is this to fetch the each records. but the problem is that i want a SQL query without using any functions like PIVOT and all. Is that possible to get the records in this form using JOIN?? Please help me out. :)

Posted 9-Feb-18 22:55pm

FireMonkey92

Updated 9-Feb-18 23:16pm

Add a Solution

Comments

Maciej Los 10-Feb-18 5:09am

Why? Using pivot for such of situation is recommended.

FireMonkey92 10-Feb-18 5:13am

i know sir but at interview they mentioned not to use PIVOT. I tried but was not able to solve that so..

2 solutions

Solution 1

PIVOT is the right method to do this, it is exactly what it is designed for.
You could do it with a JOIN - or rather with quite a few JOINs - but it would be cumbersome, long winded, less efficient and a PITA to maintain.

Keep the PIVOT: it works, and it's the right thing to use.

Posted 9-Feb-18 23:12pm

OriginalGriff

Comments

Maciej Los 10-Feb-18 5:18am

1000% agree! Instead of joins, OP may use SUM(CASE WHEN ...). I haven't tested if CASE WHEN is faster or slower than pivot, but i'm pretty sure that it's slower.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-02-09T23:15:00

First of all, please, read my comment [EIDT] and OriginalGriff's answer[/EDIT].
Here is an idea, how to achieve that without using pivot.

CASE WHEN ... END solution:

SQL

SELECT fStudentname as Student_name, EC1 ,EC2,EC3,EC4,EC5,Total =([EC1]+[EC2]+[EC3]+[EC4]+[EC5])
FROM (
    SELECT fStudentname, 
        EC1 = SUM(CASE WHEN FSubject = 'EC1' THEN Fmarks ELSE NULL), 
        EC2 = SUM(CASE WHEN FSubject = 'EC2' THEN Fmarks ELSE NULL),
        EC3 = SUM(CASE WHEN FSubject = 'EC3' THEN Fmarks ELSE NULL),
        EC4 = SUM(CASE WHEN FSubject = 'EC4' THEN Fmarks ELSE NULL),
        EC5 = SUM(CASE WHEN FSubject = 'EC5' THEN Fmarks ELSE NULL) 
    FROM Student
    GROUP BY fStudentname
) AS Src

JOIN's solution:

SQL

DECLARE @student TABLE(stuID INT, fStudentname VARCHAR(30), FSubject VARCHAR(30), Fmarks INT)

INSERT INTO @student (stuID, fStudentname, FSubject, Fmarks)
VALUES(1, 'Savita', 'EC1', 30),
(1, 'Savita', 'EC2', 19),
(1, 'Savita', 'EC3', 28),
(1, 'Savita', 'EC4', 30),
(1, 'Savita', 'EC5', 60),
(2, 'Vidya', 'EC1', 90),
(2, 'Vidya', 'EC2', 80),
(2, 'Vidya', 'EC3', 85),
(2, 'Vidya', 'EC4', 75),
(2, 'Vidya', 'EC5', 99),
(3, 'Tanesh', 'EC1', 75),
(3, 'Tanesh', 'EC2', 80),
(3, 'Tanesh', 'EC3', 85),
(3, 'Tanesh', 'EC4', 28),
(3, 'Tanesh', 'EC5', 86)

SELECT fStudentname as Student_name, EC1 ,EC2,EC3,EC4,EC5,Total =([EC1]+[EC2]+[EC3]+[EC4]+[EC5])
FROM (
    SELECT A.fStudentname, A.EC1, B.EC2, C.EC3, D.EC4, E.EC5
    FROM (
		(SELECT fStudentname, SUM(Fmarks) AS EC1 FROM @student WHERE FSubject = 'EC1' GROUP BY fStudentname) AS A 
		INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC2 FROM @student WHERE FSubject = 'EC2' GROUP BY fStudentname) AS B ON A.fStudentname = B.fStudentname
		INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC3 FROM @student WHERE FSubject = 'EC3' GROUP BY fStudentname) AS C ON A.fStudentname = C.fStudentname
		INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC4 FROM @student WHERE FSubject = 'EC4' GROUP BY fStudentname) AS D ON A.fStudentname = D.fStudentname
		INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC5 FROM @student WHERE FSubject = 'EC5' GROUP BY fStudentname) AS E ON A.fStudentname = E.fStudentname
	) 
) AS Final

Note: tested on temporary table. You have to replace @student with real table name.