Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query 2 tables putting rows into columns

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)
);
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
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 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[/code]
[code]
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
[/code]
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

65 Posts

Posted - 2013-01-16 : 17:07:17
Thank You.
Go to Top of Page
   

- Advertisement -