dynamically populating column headers in a gridview

Question

I have a vb.net website with a gridview that currently has exam questions displaying vertically in each row like this:

Name  question  answer
-----------------------
Joe   question1 answer1
Joe   question2 answer2
Joe   question3 answer3
Jill  question1 answer1
Jill  question2 answer2
Jill  question3 answer3

But I would like to change it, so that each question is a header, like this:

Name question1 question2 question3
----------------------------------
Joe  answer1   answer2   answer3
Jill answer1   answer2   answer3

This makes it more readable since each user is only listed once.

I've spent the better part of the morning googling for solutions, but really can't find anything that works.

I would like to stick with a gridview instead of rewriting all my code.

Does anyone have any suggestions?

I am actually binding my data to the gridview via some other programmers class. I am using LINQ like this:

Return (From entry In report.FetchAllEntries()
                Select questionID = entry.Question.QuestionID,
                userID = entry.Session.User.ID,
                firstName = entry.Session.User.FirstName,
                lastName = entry.Session.User.LastName,
                QuestionText = entry.Question.Stem,
                UserResponse = entry.Response.Text,
                FreeResponse = entry.ResponseText,
                SessionDate = entry.Timestamp
                 Where SessionDate.HasValue AndAlso
                               SessionDate.Value >= dateField1 AndAlso
                               SessionDate.Value <= dateField2
                Order By lastName, SessionDate, questionID

Thanks

It looks like you might be able to do that by modifying your SQL query (like doing a `PIVOT` or something). Can you show the query that's generating the data for your `GridView`? — Josh Darnell, Mar 06 '12 at 15:34
I am binding via a LINQ statement. I am not sure if I can modify the LINQ statement to switch it around like that...I edited my original post to include the LINQ. --Thanks — SkyeBoniwell, Mar 06 '12 at 15:41
You might be able to pivot the data from using another linq query: http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq — jmaglio, Mar 06 '12 at 16:25

score 1 · Accepted Answer · answered Mar 06 '12 at 20:39

You need to use group by to aggregate your results. This should work:

Dim grouped =
    From usersAnswers In
        From result In results
        Group result By result.userID Into Group
        Let answers = Group.OrderBy(Function(x) x.QuestionText).ToList()
        Select
            answers.First().firstName,
            Question1 = answers(0).UserResponse,
            Question2 = answers(1).UserResponse,
            Question3 = answers(2).UserResponse

dynamically populating column headers in a gridview

1 Answers1