SQL Rows to Columns

Question

Hi,

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.

Answer 1

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:

SELECT  *
FROM    Question
LEFT OUTER JOIN
        Response
ON      Response.QuestionId = Question.QuestionID

or, probably, this (in SQL Server 2005+, Oracle 8i+ and PostgreSQL 8.4+):

SELECT  *
FROM    (
        SELECT  q.*, ROW_NUMBER() OVER (ORDER BY questionID) AS rn
        FROM    Question q
        ) q
LEFT OUTER JOIN
        (
        SELECT  r.*, ROW_NUMBER() OVER (PARTITION BY questionID ORDER BY ResponseID) AS rn
        FROM    Response r
        ) r
ON      r.QuestionId = q.QuestionID
        AND q.rn = r.rn
ORDER BY
        q.rn, q.QuestionID

The latter query will give you results in this form (provided you have 4 questions):

rn      question      response
---          ---           ---
1     Question 1  Response 1.1
1     Question 2  Response 2.1
1     Question 3  Response 3.1
1     Question 4  Response 4.1
2     Question 1  Response 1.2
2     Question 2  Response 2.2
2     Question 3  NULL
2     Question 4  Response 4.2
3     Question 1  NULL
3     Question 2  NULL
3     Question 3  Response 3.3
3     Question 4  NULL

, 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 each rn

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 in HTML form right out of the database:

WITH    a AS
        (
        SELECT  a.*, ROW_NUMBER() OVER (PARTITION BY question_id ORDER BY id) AS rn
        FROM    answer a
        ),
        rows AS (
        SELECT  ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    answer a
        WHERE   question_id =
                (
                SELECT  TOP 1 question_id
                FROM    answer a
                GROUP BY
                        question_id
                ORDER BY
                        COUNT(*) DESC
                )
        )
SELECT  (
        SELECT  COALESCE(a.value, '')
        FROM   question q
        LEFT JOIN
                a
        ON      a.rn = rows.rn
                AND a.question_id = q.id
        FOR XML PATH ('td'), TYPE
        ) AS tr
FROM    rows
FOR XML PATH(''), ROOT('table')

See this entry in my blog for more detail:

• Dynamic pivot

Answer 2

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.