Return variable number of columns to asp.net report viewer control

Hi,

I've been tasked with generating reports for an application residing on SQL Server.

The recommended architecture I have to follow is Stored Proc  > xsd tableadapter > rdlc > report viewer.

Using a regular relational database I'd be able to do this in my sleep.  This appliction is structured around the concept of questions and answers.  For example, the information for a product is stored in the Product-answer table, the "column headings" are stored in the questions table, the questions act as the labels for the fields in the asp.net application.  The two tables are  linked using the question-id field.

I'm looking for suggestions as to how I can return to reportviewer the questions as headers in the report and the answers as the detail in the report.

Thanks! in advance

Mike
StiggySonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Sounds like you need to pivot by the question which will likely require dynamic SQL as PIVOT in SQL Server requires hardcoded column values to function.  Fortunately, Mark Wills has put together a great explanation of this as well as a stored procedure that does all the heavy lifting for you.  Please read this and see if it does everything you are looking for:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

If it looks like it is what you want, but you are having difficulties implementing it here, just post back and I or other Experts here can assist.

Best regards,

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StiggySonAuthor Commented:
thanks for pointing me in the right direction Kevin.  I think that's what i'm looking for alright.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.