Author |
Topic |
Sqlraider
Yak Posting Veteran
65 Posts |
Posted - 2013-01-16 : 15:23:03
|
I want to query 2 tables putting the rows into columns. The tables have different column types, so I'm not sure how to go about joining them. Results from one table can be in the other or not.Tough to explain so I'll show you what I have & the results.create table NESA( testYR char(9), testname varchar(30), id int, percentile smallint, testscore smallint, correctotal varchar(30));GOINSERT NESA (testYR, testname, id, percentile, testscore, correctotal)VALUES('2011-2012', 'NESA-R', 564, 88, 157, ''),('2011-2012', 'NESA-R-COMP', 564, 0, 35, 38),('2011-2012', 'NESA-R-VOCAB', 564, 0, 9, 12),('2011-2012', 'NESA-R', 636, 98, 200, ''),('2011-2012', 'NESA-R-COMP', 636, 0, 37, 38),('2011-2012', 'NESA-R-VOCAB', 636, 0, 12, 12),('2011-2012', 'NESA-R', 639, 77, 136, ''),('2011-2012', 'NESA-R-COMP', 639, 0, 31, 38),('2011-2012', 'NESA-R-VOCAB', 639, 0, 10, 12),('2011-2012', 'NESA-R', 676, 57, 111, ''),('2011-2012', 'NESA-R-COMP', 676, 0, 28, 38),('2011-2012', 'NESA-R-VOCAB', 676, 0, 8, 12),('2011-2012', 'NESA-R', 723, 98, 200, ''),('2011-2012', 'NESA-R-COMP', 723, 0, 38, 38),('2011-2012', 'NESA-R-VOCAB', 723, 0, 10, 12),('2011-2012', 'NESA-R', 726, 97, 189, ''),('2011-2012', 'NESA-R-COMP', 726, 0, 35, 38),('2011-2012', 'NESA-R-VOCAB', 726, 0, 12, 12),('2011-2012', 'NESA-R', 734, 98, 200, ''),('2011-2012', 'NESA-R-COMP', 734, 0, 37, 38),('2011-2012', 'NESA-R-VOCAB', 734, 0, 11, 12),('2011-2012', 'NESA-R', 735, 57, 111, ''),('2011-2012', 'NESA-R-COMP', 735, 0, 28, 38),('2011-2012', 'NESA-R-VOCAB', 735, 0, 8, 12);GOcreate table CSA( testYR char(9), testname varchar(30), id int, score decimal(5,2));GOINSERT CSA (testYR, testname, id, score)VALUES('2011-2012', 'CSA 1-RDG COMP', 564, 14.00),('2011-2012', 'CSA 2-RDG COMP', 564, 12.00),('2011-2012', 'CSA 3-RDG COMP', 564, 14.00),('2011-2012', 'CSA 1-RDG COMP', 639, 10.00),('2011-2012', 'CSA 2-RDG COMP', 639, 10.00),('2011-2012', 'CSA 3-RDG COMP', 639, 10.00),('2011-2012', 'CSA 1-RDG COMP', 676, 13.00),('2011-2012', 'CSA 2-RDG COMP', 676, 6.00),('2011-2012', 'CSA 3-RDG COMP', 676, 6.00),('2011-2012', 'CSA 2-RDG COMP', 726, 15.00),('2011-2012', 'CSA 4-RDG COMP', 726, 16.00),('2011-2012', 'CSA 2-RDG COMP', 734, 16.00),('2011-2012', 'CSA 3-RDG COMP', 734, 16.00),('2011-2012', 'CSA 4-RDG COMP', 734, 16.00),('2011-2012', 'CSA 2-RDG COMP', 735, 13.00),('2011-2012', 'CSA 3-RDG COMP', 735, 12.00),('2011-2012', 'CSA 1-RDG COMP', 744, 12.00),('2011-2012', 'CSA 2-RDG COMP', 744, 14.00),('2011-2012', 'CSA 3-RDG COMP', 744, 16.00),('2011-2012', 'CSA 4-RDG COMP', 744, 13.00);GOcreate table NAMES( id int, studentname varchar(50));GOINSERT NAMES (id, studentname)VALUES(564, 'Smith, Joe'),(636, 'Crowly, Karen'),(639, 'Abrem, Stan'),(676, 'Zena, Star'),(723, 'Star, Jenna'),(726, 'Queen, Beth'),(734, 'Homer, John'),(735, 'Lance, Roman'),(744, 'Wilson, Doris');GOselect ne.id, n.studentname, ne.testname, ne.testscore, ne.correctotal,CASE ne.testname when 'NESA-R' then ne.testscore end as 'NESA Score',CASE ne.testname when 'NESA-R-COMP' then ((ne.testscore / ne.correctotal) * 100) end as 'NESA COMP %',CASE ne.testname when 'NESA-R-VOCAB' then ((ne.testscore / ne.correctotal) * 100) end as 'NESA VOCAB %'from NESA as nejoin NAMES as non ne.id = n.idwhere testYR = '2011-2012'and testname in ('NESA-R ', 'NESA-R-COMP', 'NESA-R-VOCAB')order by idselect c.id, n.studentname, c.testname, CASE c.testname when 'CSA 1-RDG COMP' then c.score end as 'CSA1 Score',CASE c.testname when 'CSA 2-RDG COMP' then c.score end as 'CSA2 Score',CASE c.testname when 'CSA 3-RDG COMP' then c.score end as 'CSA3 Score',CASE c.testname when 'CSA 4-RDG COMP' then c.score end as 'CSA4 Score'from CSA as cjoin NAMES as non c.id = n.idwhere c.testYR = '2011-2012'and c.testname in ('CSA 1-RDG COMP', 'CSA 2-RDG COMP','CSA 3-RDG COMP', 'CSA 4-RDG COMP') order by id If a column in the results are NULL I want it to be blank. The calculation field should be blank if NULL otherwise in (decimal(5,2)) format.Expected results(put pipes "|" between fields:ID| Studentname| NESA Score| NESA COMP %| NESA VOCAB %| CSA1 Score| CSA2 Score| CSA3 Score| CSA4 Score564| Smith, Joe| 157| 92.10| 75.00| 14| 12| 14| NULL636| Crowly, Karen| 200| 97.36| 100| NULL| NULL| NULL| NULL639| Abrem, Stan| 136| 87.57| 83.33| 10| 10| 10| NULL676| Zena, Star| 111| 73.68| 66.66| 13| 6| 6| NULL723| Star, Jenna| 200| 100| 83.33| NULL| NULL| NULL| NULL726| Queen, Beth| 189| 92.10| 100| NULL| 15| NULL| NULL734| Homer, John| 200| 97.36| 91.66| NULL| 16| 16| 16735| Lance, Roman| 111| 73.68| 66.66| NULL| 13| 12| NULL744| Wilson, Doris| NULL| NULL| 12| 14| 16| 13 FYI - where NULL is in the results I want it to be blank and the column type on one of the calculation fields is a varchar.Need help combining the two querries to get the desired results & the calculation(it is percent) testscore is the number of answers they got correct and correctotal is the number of answers.Thanks. |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-16 : 16:51:34
|
[code]Select ID,studentname,MAX(Case When testname = 'NESA-R' then Cast(testscore as varchar(10)) Else '' End) as [NESA Score],MAX(Case When testname = 'NESA-R-COMP' then Cast(correctotal as varchar(10)) Else '' End) as [NESA COMP%],MAX(Case When testname = 'NESA-R-VOCAB' then Cast(correctotal as varchar(10)) Else '' End) as [NESA VOCAB%],MAX(Case When testname = 'CSA 1-RDG COMP' then Cast(testscore as varchar(10)) Else '' End) as [CSA1 Score],MAX(Case When testname = 'CSA 2-RDG COMP' then Cast(testscore as varchar(10)) Else '' End) as [CSA2 Score],MAX(Case When testname = 'CSA 3-RDG COMP' then Cast(testscore as varchar(10)) Else '' End) as [CSA3 Score],MAX(Case When testname = 'CSA 4-RDG COMP' then Cast(testscore as varchar(10)) Else '' End) as [CSA4 Score]from(Select ne.id, n.studentname, ne.testname, ne.testscore,round((convert(float,ne.testscore) / nullif(Convert(float,ne.correctotal),0)) * 100,2) as correctotalfrom #NESA as nejoin #NAMES as non ne.id = n.idwhere testYR = '2011-2012'and testname in ('NESA-R', 'NESA-R-COMP', 'NESA-R-VOCAB')unionSelect c.id, n.studentname, c.testname, score,''from #CSA as cjoin #NAMES as non c.id = n.idwhere c.testYR = '2011-2012'and c.testname in ('CSA 1-RDG COMP', 'CSA 2-RDG COMP','CSA 3-RDG COMP', 'CSA 4-RDG COMP'))PGroup by ID,studentname[/code][code]ID studentname NESA Score NESA COMP% NESA VOCAB% CSA1 Score CSA2 Score CSA3 Score CSA4 Score564 Smith, Joe 157.00 92.11 75 14.00 12.00 14.00 636 Crowly, Karen 200.00 97.37 100 639 Abrem, Stan 136.00 81.58 83.33 10.00 10.00 10.00 676 Zena, Star 111.00 73.68 66.67 13.00 6.00 6.00 723 Star, Jenna 200.00 100 83.33 726 Queen, Beth 189.00 92.11 100 15.00 16.00734 Homer, John 200.00 97.37 91.67 16.00 16.00 16.00735 Lance, Roman 111.00 73.68 66.67 13.00 12.00 744 Wilson, Doris 12.00 14.00 16.00 13.00[/code]If this is dynamic see my post for this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181589 |
|
|
Sqlraider
Yak Posting Veteran
65 Posts |
Posted - 2013-01-16 : 17:07:17
|
Thank You. |
|
|
|
|
|