透视表，不带文本，不进行汇总 [英] 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

我正在尝试旋转表格，以便获取相关的line ID，并使散布函数成为Questions，所以它看起来像

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

LineID |问题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，但是这种数据转换类型是数据点.这会将行数据转换为列.某些数据库具有枢轴功能，但是如果您使用的是不带枢轴功能的数据库，那么您将需要使用带有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小提琴

如果您的数据库具有数据透视功能(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小提琴

现在，如果您使用的是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)

请参见带有演示的SQL小提琴

根据您的样本数据得出的结果是:

Based on your sample data the result is:

| LINEID |  HEIGHT | OUTSIDE COLOR |
------------------------------------
|      1 | 180 3/4 |    Dark Green |

这篇关于透视表，不带文本，不进行汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！