I have a table and want to transpose its rows to columns, similar to a pivot table but without summarising.
For example I have the following tables:
Question
--QuestionID
--QuestionText
Response
--ResponseID
--ResponseText
--QuestionID
Basically I want to be able to create a dynamic table something like:
Question 1 Text | Question 2 Text | Question 3 Text
---------------------------------------------------
Response 1.1 Text | Response Text 1.2 | Response 1.3
Response 2.1 Text | Response Text 2.2 | Response 2.3
Response 3.1 Text | Response Text 3.2 | Response 3.3
Response 4.1 Text | Response Text 4.2 | Response 4.3
The main requirement would be I don't know at design time what the question text will be.
Please can someone help - I am pulling my hair out :oS
Essentially you can guarantee that there will be a response for each corresponding question in this scenario.
You cannot do it with
SQL
(except with dynamic queries), unless you know the number of columns (i. e. questions) in design time.You should pull the data you want in tabular format and then process it on client side:
or, probably, this (in
SQL Server 2005+
,Oracle 8i+
andPostgreSQL 8.4+
):The latter query will give you results in this form (provided you have
4
questions):, this is it will output the data in tabular form, with
rn
marking the row number.Each time you see the
rn
changing on the client, you just close<tr>
and open the new one.You may safely put your
<td>
's one per resultset row, since same number or rows is guaranteed to be returned for eachrn
This is quite a frequently asked question.
SQL
just not a right tool to return data with dynamic number of columns.SQL
operates on sets, and the column layout is an implicit property of a set.You should define the layout of the set you want to get in design time, just like you define the datatype of a varible in
C
.C
works with strictly defined variables,SQL
works with strictly defined sets.Note that I'm not saying it's the best method possible. It's just the way
SQL
works.Update:
In
SQL Server
, you can pull the table inHTML
form right out of the database:See this entry in my blog for more detail:
Do your grouping and aggregating first, using Quassnoi's answer as an intermediate result.
Crosstabulation should only be done when you are no longer going to be doing set oriented operatons on the results. Some SQL dialects have keywords like PIVOT, TRANSFORM or CROSSTABULATE to accomplish this, but you're probably better off using XSLT.