SQl rows to columns conversion
Posted
by Thihara
on Stack Overflow
See other posts from Stack Overflow
or by Thihara
Published on 2010-03-20T08:19:59Z
Indexed on
2010/03/20
8:31 UTC
Read the original article
Hit count: 303
sql
|sql-server-2005
Hi,
I have a table ClassAttendance and I'm using MSSQL 2005
studentID--attendanceDate---------------------------------------status
1004--------2010-03-17--------------------------------------------------0 1005--------2010-03-17--------------------------------------------------1 1006--------2010-03-17--------------------------------------------------0 1007--------2010-03-17--------------------------------------------------0 1004--------2010-03-19--------------------------------------------------0 1005--------2010-03-19--------------------------------------------------1 1006--------2010-03-19--------------------------------------------------0 1007--------2010-03-19--------------------------------------------------0 1004--------2010-03-20--------------------------------------------------1
as you can see studentID is a foreign Key for a table called StudentData and attendedDate has an unknown number of rows.
Can i get the output like below by using a query? I need the dates in one month to be columns and the value of the date columns will be values in the status column. The number of date records per studentID is the same its the number of dates in the attendanceDate filed that is unknown.
studentID---------2010-03-17--------2010-03-19------2010-03-20
1004-----------------------------0----------------------0--------------------1
etc. This is for a creating a report so I need to do it in a query. Please help if you can.
© Stack Overflow or respective owner
