Pivot Table with dynamic Columns

[kardsen]

I am trying to write a query in my .net app and I'm having trouble pivoting the table since the columns are generated dynamically (based on a month range selected by the user)

SELECT SUM(HEALTH_MTTR) AS MTTR, SUM(HEALTH_OTR) AS OTR, SUM(HEALTH_REPEAT) AS REPEAT, SUM(HEALTH_CHRONIC) AS CHRONIC, SUM(HEALTH_TOTAL) AS TOTAL, (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) As YearMonth
FROM TRT_remedy_tickets 
WHERE COMPID = @COMPID AND (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) BETWEEN @START AND @END
Group By (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7)))

Generates a table like so:
MTTR OTR REPEAT CHRONIC TOTAL YearMonth
x x x x x 2009-06
x x x x x 2009-07
x x x x x 2009-08

And I need the table to be displayed like so
TYPE 2009-06 2009-07 2009-08 2009-09
MTTR x x x x
OTR x x x x
REP x x x x
CHR x x x x
TOT x x x x

[sknake]

Use Union All and break up the select statements

[kardsen]

Well,

Couldn't get this to work any other way so what I've done is create the select statement in the vb.net codebehind file so that every time the date is changed it rebuilds the select statement.

Quite long but at least I understand the mechanics behind it.
I've also created the GridView Columns programmatically as well since each time the selectstatment changes so do the columnfields from which it should pull into.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
        If Not Page.IsPostBack Then
            ddMonths.Items.Clear()
            FillDateDDL(Date.Now.AddMonths(-12), Date.Now)

            Dim MonthSel As String = Request("Month")
            If MonthSel = "" Then
                ddMonths.SelectedIndex = 12
                LoadSelectCommand()
            Else
                ddMonths.SelectedValue = MonthSel
                LoadSelectCommand()
            End If

        End If
    End Sub

    Private Sub FillDateDDL(ByVal StartDate As Date, ByVal EndDate As Date)
        While StartDate <= EndDate
            ddMonths.Items.Add(StartDate.Year.ToString() & "-" & StartDate.Month.ToString().PadLeft(2, "0"))
            StartDate = StartDate.AddMonths(1)
        End While

    End Sub

    Protected Sub ddMonths_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddMonths.SelectedIndexChanged
        LoadSelectCommand()
    End Sub


    Sub LoadSelectCommand()
        Dim querybuilder As New StringBuilder

        querybuilder.Append("with cte (YearMonth, [1. MTTR], [2. OTR], [3. REPEAT], [4. CHRONIC], [5. TOTAL])").AppendLine()
        querybuilder.Append("as").AppendLine()
        querybuilder.Append("(").AppendLine()
        querybuilder.Append("SELECT CASE ")

        For M = 1 To 12
            Dim selectedmonth As Date = ddMonths.SelectedValue & "-01"
            selectedmonth = selectedmonth.AddMonths(-12)
            Dim LastMonth As Date = selectedmonth.AddMonths(M)
            Dim Lmonth As String = LastMonth.ToString("yy MM")
            Dim currentyear As String = Left(Lmonth, 2)
            Dim currentmonth As String = Lmonth.Substring(Lmonth.Length - 2)

            If Not M = 12 Then
                querybuilder.Append("WHEN Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) = '" & currentmonth & "' THEN '" & MonthName(currentmonth, True) & " ' + '" & currentyear & "'").AppendLine()
            Else : querybuilder.Append("WHEN Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) = '" & currentmonth & "' THEN '" & MonthName(currentmonth, True) & " ' + '" & currentyear & "' END AS [YearMonth],").AppendLine()
            End If
        Next

        querybuilder.Append("SUM(HEALTH_MTTR) AS '1. MTTR', SUM(HEALTH_OTR) AS '2. OTR', SUM(HEALTH_REPEAT) AS '3. REPEAT', SUM(HEALTH_CHRONIC) AS '4. CHRONIC', SUM(HEALTH_TOTAL) AS '5. TOTAL'").AppendLine()
        querybuilder.Append("FROM TRT_remedy_tickets ").AppendLine()
        querybuilder.Append("WHERE COMPID = @COMPID AND (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) BETWEEN ")

        For M = 1 To 12
            Dim selectedmonth As Date = ddMonths.SelectedValue & "-01"
            selectedmonth = selectedmonth.AddMonths(-12)
            Dim LastMonth As Date = selectedmonth.AddMonths(M)
            Dim Lmonth As String = LastMonth.ToString("yyyy MM")
            Dim currentyear As String = Left(Lmonth, 4)
            Dim currentmonth As String = Lmonth.Substring(Lmonth.Length - 2)

            If M = 1 Then
                querybuilder.Append("'" & currentyear & "-" & currentmonth & "' AND ")
            ElseIf M = 12 Then
                querybuilder.Append("'" & currentyear & "-" & currentmonth & "'")
            End If
        Next

        querybuilder.AppendLine()
        querybuilder.Append("Group By TRT_remedy_tickets.DATE_RESOLVED_FOR_CLOSURE").AppendLine()
        querybuilder.Append(")").AppendLine()
        querybuilder.Append(",cte2 (YearMonth, health, metric)").AppendLine()
        querybuilder.Append("as(").AppendLine()
        querybuilder.Append("select unpvt.YearMonth, unpvt.health, unpvt.metric").AppendLine()
        querybuilder.Append("from").AppendLine()
        querybuilder.Append("(").AppendLine()
        querybuilder.Append("select YearMonth, [1. MTTR], [2. OTR], [3. REPEAT], [4. CHRONIC], [5. TOTAL]").AppendLine()
        querybuilder.Append("from cte").AppendLine()
        querybuilder.Append(") as x").AppendLine()
        querybuilder.Append("unpivot").AppendLine()
        querybuilder.Append("(").AppendLine()
        querybuilder.Append("health for metric in (x.[1. MTTR], x.[2. OTR], x.[3. REPEAT], x.[4. CHRONIC], x.[5. TOTAL])")
        querybuilder.Append(") as unpvt").AppendLine()
        querybuilder.Append(")").AppendLine()
        querybuilder.Append("select pvt.metric, ")

        For M = 1 To 12
            Dim selectedmonth As Date = ddMonths.SelectedValue & "-01"
            selectedmonth = selectedmonth.AddMonths(-12)
            Dim LastMonth As Date = selectedmonth.AddMonths(M)
            Dim Lmonth As String = LastMonth.ToString("yy MM")
            Dim currentyear As String = Left(Lmonth, 2)
            Dim currentmonth As String = Lmonth.Substring(Lmonth.Length - 2)

            If Not M = 12 Then
                querybuilder.Append("pvt.[" & MonthName(currentmonth, True) & " " & currentyear & "],")
            Else : querybuilder.Append("pvt.[" & MonthName(currentmonth, True) & " " & currentyear & "]")
            End If
        Next

        querybuilder.AppendLine()
        querybuilder.Append("from").AppendLine()
        querybuilder.Append("(").AppendLine()
        querybuilder.Append("select YearMonth, health, metric").AppendLine()
        querybuilder.Append("from cte2").AppendLine()
        querybuilder.Append(") as x").AppendLine()
        querybuilder.Append("pivot").AppendLine()
        querybuilder.Append("(").AppendLine()
        querybuilder.Append("sum(x.health) for x.YearMonth in (")

        For M = 1 To 12
            Dim selectedmonth As Date = ddMonths.SelectedValue & "-01"
            selectedmonth = selectedmonth.AddMonths(-12)
            Dim LastMonth As Date = selectedmonth.AddMonths(M)
            Dim Lmonth As String = LastMonth.ToString("yy MM")
            Dim currentyear As String = Left(Lmonth, 2)
            Dim currentmonth As String = Lmonth.Substring(Lmonth.Length - 2)
            If Not M = 12 Then
                querybuilder.Append("[" & MonthName(currentmonth, True) & " " & currentyear & "], ")
            Else : querybuilder.Append("[" & MonthName(currentmonth, True) & " " & currentyear & "])").AppendLine()
            End If
        Next

        querybuilder.Append(")as pvt")

        Dim Querystring As String = querybuilder.ToString
        HealthScoreDS.SelectCommand = Querystring

        populategridview1()
    End Sub

    Sub GridView1_RowDataBound(ByVal Sender As Object, ByVal e As GridViewRowEventArgs)

        Select Case e.Row.RowIndex
            Case Is = 0
                e.Row.Cells(0).Text = "<b>MTTR</b>"
            Case Is = 1
                e.Row.Cells(0).Text = "<b>OTR</b>"
            Case Is = 2
                e.Row.Cells(0).Text = "<b>Repeat </b>"
            Case Is = 3
                e.Row.Cells(0).Text = "<b>Chronic </b>"
            Case Is = 4
                e.Row.Cells(0).Text = "<b>Overall </b>"
        End Select

        If e.Row.RowType = DataControlRowType.DataRow Then
            If e.Row.RowType = DataControlRowType.DataRow Then
                For columnIndex As Integer = 1 To e.Row.Cells.Count - 1
                    Try
                        Dim CellText = e.Row.Cells(columnIndex).Text
                        If CellText = "&nbsp;" Then
                            'MsgBox("green button")
                        ElseIf CellText > -1 Then
                            'MsgBox("green button")
                        ElseIf CellText < -1 And CellText > -5 Then
                            'MsgBox("gold button")
                        ElseIf CellText < -5 Then
                            'MsgBox("red button")
                        End If
                    Catch ex As Exception
                    End Try
                Next
            End If
        End If

    End Sub

    Protected Sub populategridview1()
        GridView1.AutoGenerateColumns = False
        GridView1.Columns.Clear()
        Dim Field As New BoundField
        Dim Col As DataControlField = Field

        Field = New BoundField
        Field.DataField = "metric"
        Field.HeaderText = " "
        Col = Field
        GridView1.Columns.Add(Col)

        For M = 1 To 12
            Dim selectedmonth As Date = ddMonths.SelectedValue & "-01"
            selectedmonth = selectedmonth.AddMonths(-12)
            Dim LastMonth As Date = selectedmonth.AddMonths(M)
            Dim Lmonth As String = LastMonth.ToString("yy MM")
            Dim currentyear As String = Left(Lmonth, 2)
            Dim currentmonth As String = Lmonth.Substring(Lmonth.Length - 2)

            Field = New BoundField
            Field.DataField = MonthName(currentmonth, True) & " " & currentyear
            Field.HeaderText = MonthName(currentmonth, True) & " " & currentyear
            Col = Field
            GridView1.Columns.Add(Col)
        Next

    End Sub

Don't be to hard on the comments to this approach. Like I said it was the most easily comprehendable solution I could come up with on my own.