SQL行到列 [英] SQL Rows to Columns
问题描述
我有一张桌子，想将其行转置为列，类似于数据透视表，但不进行汇总.
I have a table and want to transpose its rows to columns, similar to a pivot table but without summarising.
例如，我有以下表格:
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.
请有人帮忙-我正在拔头发:oS
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.
推荐答案
除非您知道设计时的列数(即问题)，否则不能使用SQL进行此操作(动态查询除外).
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
，或者可能是(在SQL Server 2005+，Oracle 8i+和PostgreSQL 8.4+中):
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
后一个查询将以这种形式为您提供结果(前提是您有4个问题):
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
，它将以表格形式输出数据，并以rn标记行号.
, this is it will output the data in tabular form, with rn marking the row number.
每次您看到客户端上的rn发生变化时，您只需关闭<tr>并打开新的<tr>.
Each time you see the rn changing on the client, you just close <tr> and open the new one.
您可以放心地将<td>放在每个结果集行中，因为保证每个rn都返回相同的行数.
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并不是返回具有动态列数的数据的正确工具.
SQL just not a right tool to return data with dynamic number of columns.
SQL对集合进行操作，列布局是集合的隐式属性.
SQL operates on sets, and the column layout is an implicit property of a set.
您应该在设计时定义要获取的集合的布局，就像在C中定义变量的数据类型一样.
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使用严格定义的变量，SQL使用严格定义的集.
C works with strictly defined variables, SQL works with strictly defined sets.
请注意，我并不是说这是最好的方法.这就是SQL的工作方式.
Note that I'm not saying it's the best method possible. It's just the way SQL works.
更新:
在SQL Server中，您可以将HTML形式的表直接从数据库中拉出:
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:
这篇关于SQL行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
