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

[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.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

[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.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

[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.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.T ables(0).Rows.C ount - 1
If
GetColumns(ds Questions.Table s(0).Rows(i).It em(0).ToString) <""
Then
SelectQuery +=
GetColumns(ds Questions.Table s(0).Rows(i).It em(0).ToString) & " AS ["
&
dsQuestions.T ables(0).Rows(i ).Item(0).ToStr ing & EndOfString(i,
CInt(dsQuesti ons.Tables(0).R ows.Count) - 1)
Else
SelectQuery += " (SELECT
SurveyExtra.S urveyExtra_Answ er FROM SurveyExtra WHERE
SurveyExtra_G uid
= '{" &
dsSurveys.Tab les(0).Rows(j). Item("Survey_Ad ditionalQS").To String &
"}'
AND SurveyExtra_Que stionGuid = '{" &
dsQuestions.T ables(0).Rows(i ).Item("Questio n_GuID").ToStri ng & "}')
AS
[" & dsQuestions.Tab les(0).Rows(i). Item(0).ToStrin g & "] "
If Not i =
(CInt(dsQuest ions.Tables(0). Rows.Count) - 1) Then
SelectQuery += ", "
End If
End If
Next
End If
End If
Query += "SELECT Survey.Survey_I d AS [Survey_Id],
Survey.Survey _GuID 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