Our application stores user responses to dynamically generated questions, so a simple event registration like system where administrators can create 1...N amount of questions per event. Or maybe I should call it a survey application.

Anyway, the resulting DB schema looks something like this (simplified):

[Question] -QuestionID (PK) -Question -QuestionPosition -EventID

[Response] -ResponseID (PK) -QuestionID (FK on Question.QuestionID) -Response -EventID -UserID

So in this case, the relation is [Question] 1 - M [Response]

Please keep in mind that this is not my actual schema, it contains more tables for normalization purposes, but for this example this two table example will work.

So, all I want to do is display a result set in a table for the responses to a particular Event (EventID).

The result set should follow a schema like this:

[UserID] | [Question 1] | [Question 2] | ... etc...

1234 | Response 1 | Response 2 | ... etc...

So each row corresponds to one user's set of responses, and the column header contains the question.

My rudimentary/inefficient approach to this is to do something like this (For brevity, I've used SQL syntax instead of LLBL predicate language):

1-Select Question.ID, Question.Question * From Question Where EventID = x 2-Create a table with results from (1) for column schema. 3-Select * From Response Where EventID = x (order by UserID, QuestionID) 4-Loop through the result set of (3), and add a row for each new UserID. 5-Next, go through each row's cell and fill in their response with (3), based on the column question.

So this would consist of 2 database calls, and a lot of loops in application code.

Is there something I can use from LLBLGen that will help me do this better? Such as some special smart transpose function?

I'm really just wondering how I could do this more efficiently, where code is kept as simple as possible.

Thanks for any help or thoughts on this.

Which DB are you using? Some will support crosstabs and different solutions that you can access through a view or stored procedure.

I second bclubb - SQL Server 2005 (for example) has the PIVOT operator now which could be used in just such a scenario. There aren't any LLBLGenPro specific features to take advantage of here because not every database supports pivoting/crosstabs.

I'm using SQL Server 2000, so I think a PIVOT is possible.

Maybe it's because I'm not a DB Expert, and more a web developer, but I have not seen any good ways of using the PIVOT to do this. PIVOT's always seem to involve aggregate columns and I've been told in other discussion forums that this would be better suited for the business logic. Anyway, thanks for the relpy. I think for now I will do it in code.