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.Table s(0).Rows.Count - 1

SelectQuery = ""
dsQuestions = GetAllQuestionN umbers("Attende es")
If dsQuestions.Tab les.Count <0 Then
If dsQuestions.Tab les(0).Rows.Cou nt <0 Then
Dim i As Integer
For i = 0 To
dsQuestions.Tab les(0).Rows.Cou nt - 1
If
GetColumns(dsQu estions.Tables( 0).Rows(i).Item (0).ToString) <"" Then
SelectQuery +=
GetColumns(dsQu estions.Tables( 0).Rows(i).Item (0).ToString) & " AS [" &
dsQuestions.Tab les(0).Rows(i). Item(0).ToStrin g & EndOfString(i,
CInt(dsQuestion s.Tables(0).Row s.Count) - 1)
Else
SelectQuery += " (SELECT
SurveyExtra.Sur veyExtra_Answer FROM SurveyExtra WHERE SurveyExtra_Gui d
= '{" &
dsSurveys.Table s(0).Rows(j).It em("Survey_Addi tionalQS").ToSt ring & "}'
AND SurveyExtra_Que stionGuid = '{" &
dsQuestions.Tab les(0).Rows(i). Item("Question_ GuID").ToString & "}') AS
[" & dsQuestions.Tab les(0).Rows(i). Item(0).ToStrin g & "] "
If Not i =
(CInt(dsQuestio ns.Tables(0).Ro ws.Count) - 1) Then
SelectQuery += ", "
End If
End If
Next
End If
End If

Query += "SELECT Survey.Survey_I d AS [Survey_Id],
Survey.Survey_G uID AS [Survey_GuID], " & SelectQuery & " FROM Survey
WHERE Survey.Survey_I d = " & dsSurveys.Table s(0).Rows(j).It em(0) & "
AND Survey_Type = 'Attendee'"

If j = (dsSurveys.Tabl es(0).Rows.Coun t - 1) Then
Query += ";"
Else
Query += " UNION "
End If

Next