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