February 8, 2008 at 10:34 pm
Hi all
Please look the next problem
SELECT Student_Name,Attedance,HalfID FROM Table1
above query providing me following result
Name1,P,1
Name1,P,2
Name1,A,1
Name1,P,2
Here half Id 1 mean first half and 2 mean second half
But I want result in followig format
StudentName FirstHalfAttedance SecondHalfAttedance
Name1 P P
Name1 A P
Warm Regards,
Shakti Singh Dulawat
Before printing, think about the environment
Do the impossible, and go home early.
February 9, 2008 at 5:53 am
If you want to get only those records with a certain HalfID value, you need to restrict your result set to those with the desired HalfID. Read up on the WHERE clause.
If you want to not include a column in the result, read up on the SELECT statement
Toni
February 9, 2008 at 6:07 am
Something like this should work. It allows a student's data to be reported even when they missed half of the year.
declare @Table1 table(
Student_Name varchar(50),
Attendance char(1),
HalfID tinyint,
PRIMARY KEY CLUSTERED(Student_Name, halfID)
)
insert into @table1( Student_Name, Attendance, HalfID )
select 'Name1', 'P', 1
UNION ALL
select 'Name1', 'P', 2
UNION ALL
select 'Name2', 'A', 1
UNION ALL
select 'Name2', 'P', 2
UNION ALL
select 'Name3', 'P', 1
select distinct
Table1.Student_name,
coalesce(Half1.Attendance, 'N/A') as FirstHalfAttendance,
coalesce(Half2.Attendance, 'N/A') as SecondHalfAttendance
from @Table1 Table1
left join @Table1 Half1
on Table1.Student_Name = Half1.Student_Name
and Half1.HalfID = 1
left join @Table1 Half2
on Table1.Student_Name = Half2.Student_Name
and Half2.HalfID = 2
In future please post some sample create table and insert statements 🙂
By the way, my first stab at select code was
select
coalesce(Half1.Student_Name, Half2.Student_Name) as Student_Name,
coalesce(Half1.Attendance, 'N/A') as FirstHalfAttendance,
coalesce(Half2.Attendance, 'N/A') as SecondHalfAttendance
from @Table1 Half1
full outer join @Table1 Half2
on Half1.Student_name = Half2.Student_name
where (Half1.halfID is null or Half1.halfID = 1)
and (Half2.halfID is null or Half2.halfID = 2)
but that doesn't report the 3rd student with only one half. I'm tired at the moment so I couldn't see why...
I assumed in your post that you meant to have two students with Name1 and Name2 rather than the one student with 4 entries. I also assume that you'll eventually have a year column to go with the half column - if so simply add it as a condition where the halves are conditioned.
February 9, 2008 at 6:12 pm
Ian, thanks for taking the time to put the test data together that really is a key to getting the full idea and answer for the question. I initially had a hard time following the request and it turned out more complex than I had thought at first.
Here is another solution that is probably closer to what you were trying to do in your first attempt (and in 18 reads versus 40):
set statistics time on
set statistics io on
declare @Table1 table(
Student_Name varchar(50),
Attendance char(1),
HalfID tinyint,
PRIMARY KEY CLUSTERED(Student_Name, halfID)
)
insert into @table1( Student_Name, Attendance, HalfID )
select 'Name1', 'P', 1
UNION ALL
select 'Name1', 'P', 2
UNION ALL
select 'Name2', 'A', 1
UNION ALL
select 'Name2', 'P', 2
UNION ALL
select 'Name3', 'P', 1
union all
select 'Name4','P', 2
select coalesce(h1.student_name, h2.student_name) student, coalesce(H1.attendance,'N/A') half1, coalesce(H2.attendance,'N/A') half2
from
(select student_name, attendance
from @table1
where halfid = 1) as H1
full join
(select student_name, attendance
from @table1
where halfid=2 ) as H2
on H1.student_name=H2.student_name
The reason you missed the Name3 in your first attempt is due to one of the WHERE expressions being null. The part (half2.halfid is null or half2.halfid=2) stops the case where the 2nd halfid is null.
select
coalesce(Half1.Student_Name, Half2.Student_Name) as Student_Name,
coalesce(Half1.Attendance, 'N/A') as FirstHalfAttendance,
coalesce(Half2.Attendance, 'N/A') as SecondHalfAttendance
from @Table1 Half1
full outer join @Table1 Half2
on Half1.Student_name = Half2.Student_name
where (Half1.halfID is null or Half1.halfID = 1)
and (Half2.halfID is null or Half2.halfID = 2)
Toni
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply