透视带有文本且没有聚合的表格 [英] Pivoting a Table with Text and no Aggregation
问题描述
我一直在查看 StackOverflow 问题,但还没有真正找到与此问题相关的问题.
I've been looking through StackOverflow questions and haven't really found one for this issue.
我有一张桌子,基本上是这样排列的:
I have a table that's arranged basically like:
Table.LineID
Table.QuestionGroup
Table.Question
Table.Answer
我想旋转"表格,但问题和答案没有任何要汇总的内容.例如,它们都只是文本字段,可能会这样写:
And I'd like to "pivot" the table, but the questions and answers don't have anything to aggregate. They're all just text fields for example, one might read:
Table.LineID = 00001
Table.QuestionGroup = Color
Table.Question = Outside Color
Table.Answer = Dark Green
Table.LineID = 00001
Table.QuestionGroup = Size
Table.Question = Height
Table.Answer = 180 3/4
我正在尝试旋转表格,以便我可以获得相关联的行 ID 并使传播函数成为问题所以它看起来像
I'm trying to pivot the table so that I can get the associated ID of line and make that the spreading function the Questions So it would look like
线路ID |问题 1 |问题 2 |等等……
LineID | Question 1 | Question 2 | Etc...
我看过这个 https://stackoverflow.com/a/7744347/839330,除了这似乎更多地参考一些VB代码(也许我错了?).有谁知道我应该如何处理这个问题?
I've looked at this https://stackoverflow.com/a/7744347/839330 except this seems to be more in reference to some VB code (maybe I'm wrong?). Does anyone have an idea on how I should approach this?
推荐答案
仅仅因为您的表中有文本数据并不意味着您不能对其使用聚合函数.
Just because you have text data in your table does not mean that you cannot use an aggregate function on it.
您没有指定您使用的是什么 RDBMS,但这种类型的数据转换是 pivot.这会将行数据转换为列.一些数据库有一个数据透视函数,但是如果你使用一个没有这个函数的数据库,那么你需要使用一个带有 CASE
表达式的聚合函数:
You did not specify what RDBMS you are using but this type of data transformation is a pivot. This converts row data into columns. Some databases has a pivot function but if you are using one without that function, then you will need to use an aggregate function with a CASE
expression:
select lineid,
max(case when question ='Height' then answer else '' end) Height,
max(case when question ='Outside Color' then answer else '' end) [Outside Color]
from yourtable
group by lineid
如果您有一个具有数据透视功能的数据库(SQL Server 2005+/Oracle 11g+),那么您的代码将类似于:
If you have a database that has the pivot function (SQL Server 2005+/Oracle 11g+), then your code will be similar to this:
select *
from
(
select lineid,
Question,
Answer
from yourtable
) src
pivot
(
max(answer)
for question in ([Height], [Outside Color])
) piv;
现在,如果您使用的是 SQL Server 2005+,并且您想将未知数量的问题转换为列,那么您可以使用类似于这样的动态 sql:
Now if you are using SQL Server 2005+ and you have an unknown number of questions that you want to turn into columns, then you can use dynamic sql similar to this:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Question)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT lineid,' + @cols + ' from
(
select lineid,
Question,
Answer
from yourtable
) x
pivot
(
max(Answer)
for Question in (' + @cols + ')
) p '
execute(@query)
根据您的示例数据,结果是:
Based on your sample data the result is:
| LINEID | HEIGHT | OUTSIDE COLOR |
------------------------------------
| 1 | 180 3/4 | Dark Green |
这篇关于透视带有文本且没有聚合的表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!