Query Problem

• Shakti Singh Dulawat

  SSC-Addicted

  Points: 483

  More actions

  February 8, 2008 at 10:34 pm

  #186489

  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.

• toniupstny

  Hall of Fame

  Points: 3986

  More actions

  February 9, 2008 at 5:53 am

  #776688

  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

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  February 9, 2008 at 6:07 am

  #776690

  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.

• toniupstny

  Hall of Fame

  Points: 3986

  More actions

  February 9, 2008 at 6:12 pm

  #776713

  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