Query Problem

[shakti5385]

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

[szlamany]

As long as a student can only have one 1 row and only one 2 row - you can fake this with a MAX() function

Select Student_Name
    ,Max(Case When HalfId=1 Then Attendance Else '' End)
    ,Max(Case When HalfId=2 Then Attendance Else '' End)
     From Table1
     Group by Student_Name

You can also create VIEWS of 1 and another VIEW of 2 attendance. And then JOIN the whole mess back together. But basically it's the same thing.

Personally I consider this taking normalization too far - if I understand what you are trying to acheive with this FirstHalf and SecondHalf...

FirstHalfAttendance is not the same "entity" as SecondHalfAttendance.

I would have designed the table to be:

StudentName
FirstHalfAttendance
SecondHalfAttendance

To avoid this very issue.

We have tables that store the marks a student gets in a class. First Marking Period Mark is stored right in the same row as 2nd MP Mark and 3rd MP Mark and 4th MP Mark and the Final Mark as well. I've seen other systems where each mark is a separate row - making the "re-association" of all a classes marks extremely difficult.

[BManke]

The data does not seem consistent to me. Student name1 seems to get an A and a P for same semester shouldnt it be:


Name1,P,1
Name1,P,2
Name2,A,1
Name2,P,2


You can denormalize this, to get the desired result, using the TRANSFORM statement in Access (other DBs need a more complicated approach) like so:

TRANSFORM first(attendance) as val
SELECT student_Name
FROM Tabelle1
GROUP BY student_name
Pivot IIF(HalfID=1,"FirstHalf_Attendance","SecondHalf_Attendance") IN ("FirstHalf_Attendance","SecondHalf_Attendance")

[szlamany]

I assumed it was Name1 and Name2 - never noticed that was all the same name...

We need more info from the OP for this one.

Also - what is the backend DB being used.