SQl行到列的转换 [英] SQl rows to columns conversion

问题描述

我有一个表ClassAttendance，并且我正在使用MSSQL 2005

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

如您所见，studentID是一个名为StudentData的表的外键，而AttendantDate的行数未知.

as you can see studentID is a foreign Key for a table called StudentData and attendedDate has an unknown number of rows.

我可以通过使用查询获得如下所示的输出吗?我需要将一个月中的日期作为列，而日期列的值将是状态列中的值.每个studentID的日期记录数与其AttenanceDate文件中未知的日期数相同.

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.

推荐答案

您可以使用PIVOT.看看本文.

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

这篇关于SQl行到列的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！