Getting database fields (rows) to appear as columns in datagrid

[dallasfreeman]

I'm looking at a quick way to get results that are displayed as rows to
display as columns.

I have three tables:-
- The Questions for the survey
- The Results of the survey (Columns are listed as question numbers)
- The Survey Extra Results (As additional questions can be listed into
the Questions table, has 3 columns (a link/id to the survey id, a
link/id to the question id, and the answer the user gave).

I need to list the results of the survey and the extra results for the
survey on one row in a datagrid for each survey.

Any ideas?

[dallasfreeman]

I currently have the following which basically makes a SQL query for
every single row required and makes a union, however this query can
easily get too complex and when 50+ surveys have been created, it will
start to lagg.

Dim j As Integer
dsSurveys = GetAllSurveys("Attendee")
For j = 0 To dsSurveys.Tables(0).Rows.Count - 1

SelectQuery = ""
dsQuestions = GetAllQuestionNumbers("Attendees")
If dsQuestions.Tables.Count <0 Then
If dsQuestions.Tables(0).Rows.Count <0 Then
Dim i As Integer
For i = 0 To
dsQuestions.Tables(0).Rows.Count - 1
If
GetColumns(dsQuestions.Tables(0).Rows(i).Item(0).T oString) <"" Then
SelectQuery +=
GetColumns(dsQuestions.Tables(0).Rows(i).Item(0).T oString) & " AS [" &
dsQuestions.Tables(0).Rows(i).Item(0).ToString & EndOfString(i,
CInt(dsQuestions.Tables(0).Rows.Count) - 1)
Else
SelectQuery += " (SELECT
SurveyExtra.SurveyExtra_Answer FROM SurveyExtra WHERE SurveyExtra_Guid
= '{" &
dsSurveys.Tables(0).Rows(j).Item("Survey_Additiona lQS").ToString & "}'
AND SurveyExtra_QuestionGuid = '{" &
dsQuestions.Tables(0).Rows(i).Item("Question_GuID" ).ToString & "}') AS
[" & dsQuestions.Tables(0).Rows(i).Item(0).ToString & "] "
If Not i =
(CInt(dsQuestions.Tables(0).Rows.Count) - 1) Then
SelectQuery += ", "
End If
End If
Next
End If
End If

Query += "SELECT Survey.Survey_Id AS [Survey_Id],
Survey.Survey_GuID AS [Survey_GuID], " & SelectQuery & " FROM Survey
WHERE Survey.Survey_Id = " & dsSurveys.Tables(0).Rows(j).Item(0) & "
AND Survey_Type = 'Attendee'"

If j = (dsSurveys.Tables(0).Rows.Count - 1) Then
Query += ";"
Else
Query += " UNION "
End If

Next

[GhostInAK]

Hello dallasfreeman,

kinda sounds like what you want is a crosstab query.

-Boo

I currently have the following which basically makes a SQL query for
every single row required and makes a union, however this query can
easily get too complex and when 50+ surveys have been created, it will
start to lagg.

Dim j As Integer
dsSurveys = GetAllSurveys("Attendee")
For j = 0 To dsSurveys.Tables(0).Rows.Count - 1
SelectQuery = ""
dsQuestions = GetAllQuestionNumbers("Attendees")
If dsQuestions.Tables.Count <0 Then
If dsQuestions.Tables(0).Rows.Count <0 Then
Dim i As Integer
For i = 0 To
dsQuestions.Tables(0).Rows.Count - 1
If
GetColumns(dsQuestions.Tables(0).Rows(i).Item(0).T oString) <"" Then
SelectQuery +=
GetColumns(dsQuestions.Tables(0).Rows(i).Item(0).T oString) & " AS [" &
dsQuestions.Tables(0).Rows(i).Item(0).ToString & EndOfString(i,
CInt(dsQuestions.Tables(0).Rows.Count) - 1)
Else
SelectQuery += " (SELECT
SurveyExtra.SurveyExtra_Answer FROM SurveyExtra WHERE SurveyExtra_Guid
= '{" &
dsSurveys.Tables(0).Rows(j).Item("Survey_Additiona lQS").ToString & "}'
AND SurveyExtra_QuestionGuid = '{" &
dsQuestions.Tables(0).Rows(i).Item("Question_GuID" ).ToString & "}') AS
[" & dsQuestions.Tables(0).Rows(i).Item(0).ToString & "] "
If Not i =
(CInt(dsQuestions.Tables(0).Rows.Count) - 1) Then
SelectQuery += ", "
End If
End If
Next
End If
End If
Query += "SELECT Survey.Survey_Id AS [Survey_Id],
Survey.Survey_GuID AS [Survey_GuID], " & SelectQuery & " FROM Survey
WHERE Survey.Survey_Id = " & dsSurveys.Tables(0).Rows(j).Item(0) & "
AND Survey_Type = 'Attendee'"

If j = (dsSurveys.Tables(0).Rows.Count - 1) Then
Query += ";"
Else
Query += " UNION "
End If
Next

[dallasfreeman]

Thanks for your help

That's a function within Access though, this query has to be a straight
SQL.

or does the Crosstab Query eventually show the SQL behind it

By the way, the number of columns can differ each time, so the query
has to be able to handle x number of additional questions


GhostInAK wrote:

Hello dallasfreeman,

kinda sounds like what you want is a crosstab query.

-Boo

I currently have the following which basically makes a SQL query for
every single row required and makes a union, however this query can
easily get too complex and when 50+ surveys have been created, it will
start to lagg.

Dim j As Integer
dsSurveys = GetAllSurveys("Attendee")
For j = 0 To dsSurveys.Tables(0).Rows.Count - 1
SelectQuery = ""
dsQuestions = GetAllQuestionNumbers("Attendees")
If dsQuestions.Tables.Count <0 Then
If dsQuestions.Tables(0).Rows.Count <0 Then
Dim i As Integer
For i = 0 To
dsQuestions.Tables(0).Rows.Count - 1
If
GetColumns(dsQuestions.Tables(0).Rows(i).Item(0).T oString) <"" Then
SelectQuery +=
GetColumns(dsQuestions.Tables(0).Rows(i).Item(0).T oString) & " AS [" &
dsQuestions.Tables(0).Rows(i).Item(0).ToString & EndOfString(i,
CInt(dsQuestions.Tables(0).Rows.Count) - 1)
Else
SelectQuery += " (SELECT
SurveyExtra.SurveyExtra_Answer FROM SurveyExtra WHERE SurveyExtra_Guid
= '{" &
dsSurveys.Tables(0).Rows(j).Item("Survey_Additiona lQS").ToString & "}'
AND SurveyExtra_QuestionGuid = '{" &
dsQuestions.Tables(0).Rows(i).Item("Question_GuID" ).ToString & "}') AS
[" & dsQuestions.Tables(0).Rows(i).Item(0).ToString & "] "
If Not i =
(CInt(dsQuestions.Tables(0).Rows.Count) - 1) Then
SelectQuery += ", "
End If
End If
Next
End If
End If
Query += "SELECT Survey.Survey_Id AS [Survey_Id],
Survey.Survey_GuID AS [Survey_GuID], " & SelectQuery & " FROM Survey
WHERE Survey.Survey_Id = " & dsSurveys.Tables(0).Rows(j).Item(0) & "
AND Survey_Type = 'Attendee'"

If j = (dsSurveys.Tables(0).Rows.Count - 1) Then
Query += ";"
Else
Query += " UNION "
End If
Next

[GhostInAK]

Hello dallasfreeman,

SQL Server 2005 supports the PIVOT and UNPIVOT keywords for crasstab queries.

-Boo

Thanks for your help

That's a function within Access though, this query has to be a
straight SQL.

or does the Crosstab Query eventually show the SQL behind it

By the way, the number of columns can differ each time, so the query
has to be able to handle x number of additional questions

GhostInAK wrote:

>Hello dallasfreeman,

kinda sounds like what you want is a crosstab query.

-Boo

>>I currently have the following which basically makes a SQL query for
every single row required and makes a union, however this query can
easily get too complex and when 50+ surveys have been created, it
will start to lagg.

Dim j As Integer
dsSurveys = GetAllSurveys("Attendee")
For j = 0 To dsSurveys.Tables(0).Rows.Count - 1
SelectQuery = ""
dsQuestions = GetAllQuestionNumbers("Attendees")
If dsQuestions.Tables.Count <0 Then
If dsQuestions.Tables(0).Rows.Count <0 Then
Dim i As Integer
For i = 0 To
dsQuestions.Tables(0).Rows.Count - 1
If
GetColumns(dsQuestions.Tables(0).Rows(i).Item(0) .ToString) <""
Then
SelectQuery +=
GetColumns(dsQuestions.Tables(0).Rows(i).Item(0) .ToString) & " AS ["
&
dsQuestions.Tables(0).Rows(i).Item(0).ToString & EndOfString(i,
CInt(dsQuestions.Tables(0).Rows.Count) - 1)
Else
SelectQuery += " (SELECT
SurveyExtra.SurveyExtra_Answer FROM SurveyExtra WHERE
SurveyExtra_Guid
= '{" &
dsSurveys.Tables(0).Rows(j).Item("Survey_Additio nalQS").ToString &
"}'
AND SurveyExtra_QuestionGuid = '{" &
dsQuestions.Tables(0).Rows(i).Item("Question_GuI D").ToString & "}')
AS
[" & dsQuestions.Tables(0).Rows(i).Item(0).ToString & "] "
If Not i =
(CInt(dsQuestions.Tables(0).Rows.Count) - 1) Then
SelectQuery += ", "
End If
End If
Next
End If
End If
Query += "SELECT Survey.Survey_Id AS [Survey_Id],
Survey.Survey_GuID AS [Survey_GuID], " & SelectQuery & " FROM Survey
WHERE Survey.Survey_Id = " & dsSurveys.Tables(0).Rows(j).Item(0) & "
AND Survey_Type = 'Attendee'"
If j = (dsSurveys.Tables(0).Rows.Count - 1) Then
Query += ";"
Else
Query += " UNION "
End If
Next