PIVOT 行到返回超过 1 个值的列

2022-01-22 00:00:00 pivot sql tsql sql-server

我目前正在开发一个有 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 Fiddle with Demo.这将给出一个结果:

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

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

相关文章