Dynamic SQL for GridView? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL for GridView?

Author

Topic

stronius
Starting Member

8 Posts

Posted - 2010-07-22 : 12:43:58

Hello,
I have an application that creates a dynamic SQL command which is binded to a gridview. The gridview is populated with 2 rows comparing customer data over 2 different time periods. I'm trying to add a third row which will display the change between the 2 time periods, for example, if time period one displays 109 for the 'RDO' column, and time period 2 displays 104 for the 'RDO' column, I was the 3rd row to display '-5'. Any ideas on how to achieve this?

Here is the code I use to determine the distinct field values, put them into columns, and display their count...
[CODE]
' Get distinct field values to use for columns
Dim Cols As String = Nothing
Dim cmdText As String = Nothing
Dim Builder1 As New StringBuilder()
Using SqlConn As SqlConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("sqlConnection").ToString)
Builder1.Append("DECLARE @Cols varchar(max);")
Builder1.Append("WITH DistinctCols AS (SELECT DISTINCT [")
Builder1.Append(Field)
Builder1.Append("] FROM [dbo].[v_CustomerBatchData])")
Builder1.Append("SELECT @Cols = COALESCE(@Cols + ',', '') + [")
Builder1.Append(Field)
Builder1.Append("] FROM DistinctCols SELECT @Cols")
cmdText = Builder1.ToString
Dim SqlCmd As SqlCommand = New SqlCommand(cmdText, SqlConn)
SqlConn.Open()
Try
Cols = SqlCmd.ExecuteScalar
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
SqlCmd.Dispose()
SqlConn.Close()
End Try
End Using

'Populate Gridview
Dim selCommand As String = "SELECT CONVERT(VARCHAR,[Batch_ID]) Batch, CONVERT(VARCHAR,[Date]) BatchDate,"
Dim Columns() As String = Nothing
Columns = Cols.Split(",")
For Each c In Columns
selCommand += "CONVERT(VARCHAR, COUNT(CASE [" & Field & "] WHEN '" & c & "' THEN [" & Field & "] END)) '" & c & "',"
selCommand += "CONVERT(VARCHAR, CONVERT(Numeric(10,2), COUNT(CASE [" & Field & "] WHEN '" & c & "' THEN [" & Field & "] END) * 100.00 / (SELECT COUNT([" & Field & "]) FROM [v_CustomerBatchData] WHERE [Batch_ID] = " & Batch2 & "))) '%',"
Next
selCommand = selCommand.Substring(0, selCommand.LastIndexOf(","))
selCommand += " FROM dbo.v_CustomerBatchData WHERE [Batch_ID] = " & Batch1 & " OR [Batch_ID] = " & Batch2 & " GROUP BY [Batch_ID], [Date]"
compareData.SelectCommand = selCommand
gvCompare.DataBind()
gvCompare.Visible = True
[/CODE]

Currently, this is the result I get ...
[CODE]
Batch BatchDate RDP % DYE %1 ROT %2 RDO %3 RGAS %4 STA %5 CHO %6 CPR %7
1 June 30 639 36.37 2 0.11 1 0.06 1093 62.21 4 0.23 8 0.46 1 0.06 9 0.51
16 July 30 648 36.88 2 0.11 1 0.06 1084 61.70 4 0.23 8 0.46 1 0.06 9 0.51
[/CODE]

This is the result I want...
[CODE]
Batch BatchDate RDP % DYE %1 ROT %2 RDO %3 RGAS %4 STA %5 CHO %6 CPR %7
1 June 30 639 36.37 2 0.11 1 0.06 1093 62.21 4 0.23 8 0.46 1 0.06 9 0.51
16 July 30 648 36.88 2 0.11 1 0.06 1084 61.70 4 0.23 8 0.46 1 0.06 9 0.51
Change +9 +0.51 ... etc.. etc.. etc..
[/CODE]

Any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-22 : 12:48:53

Please do not cross post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147675

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources