SQL 行到列
我有一个表，想将其行转换为列，类似于数据透视表，但不进行汇总.
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> 并打开新的.
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:
- 动态枢轴
相关文章