select row values to be column name

[fotokard]

I am new to sql query and have a question.

I would like to display row column values as column names.

questions table consisted of
question_id, group_id, question

response table consisted of
response_id, group_id, question_id, answer

I would like to display the answer responses under the columns of
questions : such as
response_id, question(1), question(2), question(3)...

It will be greatly appreciated if someone help me to get this down.

Jason

[David Portas]

What you are asking for is called a cross-tab report or pivot. Its purpose
is to present data for display or printing and there are good reasons why it
makes sense to do this in your client application or reporting tool rather
than SQL. SQL Server is designed and optimised for the data tier of an
N-tier architecture. Presentational functions belong elsewhere.

One problem with your requirement is that the number, name and datatype of
columns in a query is usually static. In order to change the columns
returned at runtime you will probably have to use Dynamic SQL. Most of the
time you should try to avoid putting dynamic SQL in your application because
doing so has certain performance, security and other implications that make
dynamic code undesirable in many cases.

Here are some references on how to produce cross-tab reports:
http://www.aspfaq.com/show.asp?id=2462

Here's a an article on dynamic SQL that explains some of the problems and
the correct and incorrect uses of dynamic code:
http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--

[fotokard]

Wow!

Thank you very much.

I did not know them.

I will check them out and try to find the best for me.

Jason

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!