SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query 2 tables putting rows into columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 01/16/2013 :  15:23:03  Show Profile  Reply with Quote
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)
);
GO
INSERT 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)
;
GO

create table CSA
(
	testYR char(9),
	testname varchar(30),
	id	int,
	score decimal(5,2)
);
GO
INSERT 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)
;
GO

create table NAMES
(
	id	int,
	studentname varchar(50)
);
GO
INSERT 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')
;
GO




select 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 ne
join NAMES as n
on ne.id = n.id
where testYR = '2011-2012'
and testname in ('NESA-R ', 'NESA-R-COMP', 'NESA-R-VOCAB')
order by id

select 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 c
join NAMES as n
on c.id = n.id
where 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 Score

564| Smith, Joe| 157| 92.10| 75.00| 14| 12| 14| NULL
636| Crowly, Karen| 200| 97.36| 100| NULL| NULL| NULL| NULL
639| Abrem, Stan| 136| 87.57| 83.33| 10| 10| 10| NULL
676| Zena, Star| 111| 73.68| 66.66| 13| 6| 6| NULL
723| Star, Jenna| 200| 100| 83.33| NULL| NULL| NULL| NULL
726| Queen, Beth| 189| 92.10| 100| NULL| 15| NULL| NULL
734| Homer, John| 200| 97.36| 91.66| NULL| 16| 16| 16
735| Lance, Roman| 111| 73.68| 66.66| NULL| 13| 12| NULL
744| 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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/16/2013 :  16:51:34  Show Profile  Reply with Quote
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 correctotal
from #NESA as ne
join #NAMES as n
on ne.id = n.id
where testYR = '2011-2012'
and testname in ('NESA-R', 'NESA-R-COMP', 'NESA-R-VOCAB')
union
Select c.id, n.studentname, c.testname, score,''
from #CSA as c
join #NAMES as n
on c.id = n.id
where c.testYR = '2011-2012'
and c.testname in ('CSA 1-RDG COMP', 'CSA 2-RDG COMP',
'CSA 3-RDG COMP', 'CSA 4-RDG COMP')
)P
Group by ID,studentname


ID	studentname	NESA Score	NESA COMP%	NESA VOCAB%	CSA1 Score	CSA2 Score	CSA3 Score	CSA4 Score
564	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.00
734	Homer, John	200.00	97.37	91.67		16.00	16.00	16.00
735	Lance, Roman	111.00	73.68	66.67		13.00	12.00	
744	Wilson, Doris				12.00	14.00	16.00	13.00

If this is dynamic see my post for this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181589
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 01/16/2013 :  17:07:17  Show Profile  Reply with Quote
Thank You.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000