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?

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_AdditionalQS").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

Hello dallasfreeman,

kinda sounds like what you want is a crosstab query.

-Boo

Show quoteHide quote

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:
Show quoteHide quote

Hello dallasfreeman,

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

-Boo

Show quoteHide quote

Update problem ADO vbnet 2005
Remoting - Some methods hang on certain setups.
Showing a form from within a thread
Hyperlink and Label
Update a structure when another structure changes
Wildcards
Dll basic question
Overwriting a file
Random
Databinding datagridview to textbox