SQl rows to columns conversion

Question

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.

Answer 1

You could use PIVOT. Take a look at this article.

Answer 2

Perhaps something using the SQL 2005 PIVOT clause? (see also: msdn)

Answer 3

Use a Matrix control in the report (or CrossTab in an Access report, or equivalent in your client tool)

SQL is a fixed column definition/contract language and you don't know how may columns you'll have. It's not really a SQL problem but a presentation problem.

Unless you use dynamic SQL PIVOT... not the SQL Server 2005+ PIVOT command which again is fixed, known columns

Answer 4

Thank you both of you dudes!! I found a stored procedure that will do what i want dynamically.... Here's the link

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829_mem1,00.html?mboxConv=searchCIO_RegActivate_Submit&

Thanx again for your help!