将PIVOT行转换为具有超过1个值的列 [英] PIVOT rows to columns with more than 1 value returned

问题描述

我目前正在一个具有2个表的系统上工作，

I am currently working on a system that has 2 tables set up like so:

Table_1
-------
ID
Table2ID
Value



Table_2
--------
ID
ColumnName

每个表的一些模拟结果:

Some mock results from each table:

Table_1

Table2ID   |   Value
---------------
1          |   ABCD
1          |   EFGH
1          |   IJKL
2          |   MNOP
2          |   QRST
2          |   UVWX


Table_2

ID   |   ColumnName
--------------------
1    |   First_Set
2    |   Second_Set

因此，我有以下查询，试图将Table_2的行结果转换为列

So, I have the following query, attempting to turn Table_2's row results into columns

SELECT *
FROM(
SELECT B.ColumnName, A.Value
FROM Table_1 AS A 
     INNER JOIN Table_2 AS B ON A.Table2ID = B.ID
     ) AS P
   PIVOT
   (
       min(P.Value)
       for P.ColumnName in ([First_Set], [Second_Set])
   ) AS PIV

问题是，按照编写的方式，我只得到一个结果.我返回的值将是这样的:

The problem is that, as it's written, I get back a single result. My returned value would be something like this:

    First_Set  |  Second_Set
    -------------------------
    ABCD       |  MNOP

我想要的是每一列的所有结果，但是我还没有找到让我做到这一点的使用PIVOT的方法.

What I want is ALL of the result for each column, but I haven't been able to find a method of using PIVOT that lets me do that.

有人对将行变成列然后为每列接收多个结果的最佳方法提出建议吗?

Does anyone have a suggestion on the best way to turn rows into columns and then receive multiple results for each column?

推荐答案

PIVOT需要使用聚合函数来获取结果，在这种情况下，您正在使用min函数，该函数在您的查询中将仅返回First_Set和Second_Set的一个值.我建议您添加一列，以在应用PIVOT时使行保持清晰.

PIVOT requires the use of an aggregate function to get the result, in your case you are using the min function which, in your query, will return only one value for First_Set and Second_Set. I would suggest including a column that will be used to keep the rows distinct when applying the PIVOT.

对于您的数据，我建议使用row_number()为组中的每个项目生成一个唯一值.然后，该值将用于PIVOT的分组方面:

For your data, I would suggest using row_number() to generate a unique value for each item in the sets. This value will then be used in the grouping aspect of the PIVOT:

SELECT [First_Set], [Second_Set]
FROM
(
  SELECT B.ColumnName, A.Value
    , row_number() over(partition by a.Table2ID
                        order by a.Value) seq
  FROM Table_1 AS A 
  INNER JOIN Table_2 AS B 
    ON A.Table2ID = B.ID
) AS P
PIVOT
(
  min(P.Value)
  for P.ColumnName in ([First_Set], [Second_Set])
) AS PIV;

请参见带有演示的SQL小提琴.这样会得到结果:

See SQL Fiddle with Demo. This will give a result:

| FIRST_SET | SECOND_SET |
|      ABCD |       MNOP |
|      EFGH |       QRST |
|      IJKL |       UVWX |

这篇关于将PIVOT行转换为具有超过1个值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！