wxvbbo

 Dim connString As String = System.Configuration.ConfigurationManager.AppSettings("Connection").ToString()
        Dim conn As New SqlConnection(connString)
        conn.Open()

        '塞Column
        Dim sql As New StringBuilder()
        sql.Append(" select courseid,coursesubject from course ")

        Dim dap As SqlDataAdapter
        dap = New SqlDataAdapter(sql.ToString(), connString)

        Dim dt As New DataTable
        dap.Fill(dt)

        DynamicGridView.Columns.Clear()

        Dim nameField As New TemplateField()

        nameField.ItemTemplate = New gvTemplate(DataControlRowType.DataRow, "name")
        nameField.HeaderTemplate = New gvTemplate(DataControlRowType.Header, "name")

        DynamicGridView.Columns.Add(nameField)

        For i As Integer = 0 To dt.Rows.Count - 1
            Dim customField As New TemplateField()

            customField.ItemTemplate = New gvTemplate(DataControlRowType.DataRow, dt.Rows(i)("coursesubject").ToString())
            customField.HeaderTemplate = New gvTemplate(DataControlRowType.Header, dt.Rows(i)("coursesubject").ToString())
           
            DynamicGridView.Columns.Add(customField)
        Next

'將課程名稱進行Pivot的置換

        Dim s As String = String.Empty

        For i As Integer = 0 To dt.Rows.Count - 1

            s = s + String.Format(" max(case when courseid='{0}' then status else null end) as '{1}',", dt.Rows(i)("Courseid").ToString(), dt.Rows(i)("CourseSubject").ToString())

        Next

        sql.Remove(0, sql.Length)

        '將置換後的資料塞給GridView呈現
        If s.Length > 1 Then
            s = s.Substring(0, s.Length - 1)
            sql.AppendFormat(" select id,name,{0} ", s)
        Else
            sql.Append(" select id, name ")
        End If

        sql.Append(" from ( ")
        sql.Append(" select id,name,coursesubject, ")
        sql.Append(" (case when isExam = 1 then 'Quiz' when isLearned = 1 then 'Learned' when isLearned = 0 then 'Not Yet' else 'Assign' end ) as status,course.courseid ")
        sql.Append(" from membername cross join course left outer join courserecord on course.courseid = courserecord.courseid and membername.id = courserecord.username ")
        sql.Append(" ) as tempTable")
        sql.Append(" group by id, name order by id")

        dt.Clear()
        dap = New SqlDataAdapter(sql.ToString(), connString)
        dap.Fill(dt)

        DynamicGridView.DataSource = dt
        DynamicGridView.DataBind()