August 19, 2012 at 3:52 am
Hello friends,
I hope every one is in good condition upon reading this forum. I am finding a hard time combining these tables.
PERSON table:
PID LN FN
1 BUNNY BUGS
2 COYOTE WILE
3 DUCK DAFFY
STUDENT table:
PID STUDNUM
1 S000001
3 S000045
EXAM_RESULTS table:
PERSON_ID EXAM_DATE SUBJECT_ID SCORE GRADE
1 2012-08-08 1 98 A+
1 2012-08-08 2 89 B+
2 2011-11-11 1 95 A
2 2011-11-11 2 85 B-
SUBJECTS table:
SUBJECT_ID DESC
1 ALGEBRA
2 PHYSICS
I want to combine columns in such way that If I search for exam results of Bugs Bunny, the expected result would be:
STUDNUM FN FN EXAM_DATE SUBJ SCORE GRADE SUBJ SCORE GRADE
S000001 BUGS BUNNY 2012-08-08 ALGEBRA 98 A+ PHYSICS 89 B+
Anyone has an idea what combination of SELECT statement to use, I tried the INNER JOIN but this is the result:
STUDNUM FN FN EXAM DATE SUBJ SCORE GRADE
S000001 BUGS BUNNY 2012-08-08 ALGEBRA 98 A+
S000001 BUGS BUNNY 2012-08-08 PHYSICS 89 B+
I want to be it in a just one line, or row...
Then I will use the query to populate my DataGraidView in my simple VB .NET application.
Thank you and more power.
Respectfully Yours,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
August 20, 2012 at 3:35 am
This is a good technique for converting rows into columns. It was posted by someone else on SSC a few days ago. If you want to convert many rows you could also use CROSS APPLY.
IF OBJECT_ID('dbo.Person') IS NOT NULL
DROP TABLE dbo.Person;
IF OBJECT_ID('dbo.Student') IS NOT NULL
DROP TABLE dbo.Student;
IF OBJECT_ID('dbo.Exam_Results') IS NOT NULL
DROP TABLE dbo.Exam_Results;
IF OBJECT_ID('dbo.Subjects') IS NOT NULL
DROP TABLE dbo.Subjects;
CREATE TABLE dbo.Person
(
PID int,
LN varchar(30),
FN varchar(30)
);
INSERT INTO dbo.Person VALUES (1, 'BUNNY', 'BUGS');
INSERT INTO dbo.Person VALUES (2, 'COYOTE', 'WILE');
INSERT INTO dbo.Person VALUES (3, 'DUCK', 'DAFFY');
CREATE TABLE dbo.Student
(
PID int,
STUDNUM varchar(30)
);
INSERT INTO dbo.Student VALUES (1, 'S000001');
INSERT INTO dbo.Student VALUES (3, 'S000045');
CREATE TABLE dbo.Exam_Results
(
PID int,
EXAM_DATE datetime,
SUBJECT_ID int,
SCORE int,
GRADE char(2)
);
INSERT INTO dbo.Exam_Results VALUES (1, '2012-08-08', 1, 98, 'A+');
INSERT INTO dbo.Exam_Results VALUES (1, '2012-08-08', 2, 89, 'B+');
INSERT INTO dbo.Exam_Results VALUES (2, '2011-11-11', 1, 95, 'A');
INSERT INTO dbo.Exam_Results VALUES (2, '2011-11-11', 2, 85, 'B-');
CREATE TABLE dbo.Subjects
(
SUBJECT_ID int,
[DESC] varchar(30)
);
INSERT INTO dbo.Subjects VALUES (1, 'ALGEBRA');
INSERT INTO dbo.Subjects VALUES (2, 'PHYSICS');
SELECT
STU.STUDNUM,
PER.LN,
PER.FN,
EXA.EXAM_DATE,
SUBJ1 = MAX(CASE WHEN EXA.SUBJECT_ID = 1 THEN [DESC] ELSE NULL END),
SCORE1 = MAX(CASE WHEN EXA.SUBJECT_ID = 1 THEN SCORE ELSE NULL END),
GRADE1 = MAX(CASE WHEN EXA.SUBJECT_ID = 1 THEN GRADE ELSE NULL END),
SUBJ2 = MAX(CASE WHEN EXA.SUBJECT_ID = 2 THEN [DESC] ELSE NULL END),
SCORE2 = MAX(CASE WHEN EXA.SUBJECT_ID = 2 THEN SCORE ELSE NULL END),
GRADE2 = MAX(CASE WHEN EXA.SUBJECT_ID = 2 THEN GRADE ELSE NULL END)
FROM
dbo.Student STU
INNER JOIN dbo.Person PER ON PER.PID = STU.PID
INNER JOIN dbo.Exam_Results EXA ON EXA.PID = PER.PID
INNER JOIN dbo.Subjects SUB ON SUB.SUBJECT_ID = EXA.SUBJECT_ID
GROUP BY
STU.STUDNUM,
PER.LN,
PER.FN,
EXA.EXAM_DATE;
If you set up proper test data yourself, you're more likely to get an answer...
August 20, 2012 at 4:31 am
laurie-789651 (8/20/2012)
If you want to convert many rows you could also use CROSS APPLY.
Did you mean PIVOT?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 20, 2012 at 6:45 am
That's correct.
August 24, 2012 at 1:47 am
Dear laurie-789651 and dwain.c,
Thank you, thank you, thank you!!! 😀
Now I could export the result/s smoothly in my [font="Courier New"] MSExcel[/font] file.
I have another question "connected" with this one (but not on the title though), should it have a new thread or a continuation to this thread?
I wish everyone have a nice day ahead...
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply