SQL Server将行分为列 [英] SQL Server making rows into columns

问题描述

我正在尝试获取我拥有的三个表，并以用户要求我执行的方式显示数据.表格看起来像这样. (我应该补充一点，我正在使用MS SQL Server)

I'm trying to take three tables that I have and show the data in a way the user asked me to do it. The tables look like this. (I should add that I am using MS SQL Server)

第一张表:该ID为varchar，因为它是ID，它们用于标识资产，并且使用数字和字母.

First Table: The ID is varchar, since it's an ID they use to identify assets and they use numbers as well as letters.

    aID| status | group   |
    -----------------------
    1  |   acti |  group1 |
    2  |   inac |  group2 |
   A3  |   acti |  group1 |

第二张表:此表是固定的.它有大约20个值，并且ID都是数字

Second Table: This table is fixed. It has around 20 values and the IDs are all numbers

    atID| traitname  |
    ------------------
     1  |   trait1   |
     2  |   trait2   |
     3  |   trait3   |

第三张表:此表用于标识第一张表中的资产具有的特征.与上表中的字段具有相同名称的字段显然已链接.

Third Table: This table is used to identify the traits the assets in the first table have. The fields that have the same name as fields in the above tables are obviously linked.

tID|   aID  |   atID |   trait   |
----------------------------------
1  |   1    |    1   |   NAME    |
2  |   1    |    2   |   INFO    |
3  |   2    |    3   |   GOES    |
4  |   2    |    1   |   HERE    |
5  |   A3   |    2   |   HAHA    |

现在，用户希望程序以以下格式输出数据:

Now, the user wants the program to output the data in the following format:

aID| status | group  | trait1 | trait2 | trait 3
-------------------------------------------------
1  |  acti  |  group1 |  NAME |  INFO  | NULL
2  |  inac  |  group2 |  HERE |  NULL  | GOES
A3 |  acti  |  group1 |  NULL |  HAHA  | NULL

我知道要实现这一点，我必须在SQL中使用Pivot命令.但是，我已经阅读并试图理解它，但似乎还是无法理解.尤其是要求最大值的部分.我不明白为什么我需要那个MAX.

I understand that to achieve this, I have to use the Pivot command in SQL. However, I've read and tried to understand it but I just can't seem to get it. Especially the part where it asks for a MAX value. I don't get why I need that MAX.

此外，我所看到的示例是针对一张表的.我不确定是否可以用三个桌子来做.我确实有一个查询，将所有三个查询与我需要的信息结合在一起.但是，我不知道如何从那里继续.请，对此的任何帮助将不胜感激.谢谢.

Also, the examples I've seen are for one table. I'm not sure if I can do it with three tables. I do have a query that joins all three of them with the information I need. However, I don't know how to proceed from there. Please, any help with this will be appreciated. Thank you.

推荐答案

有几种方法可以获取结果，包括使用PIVOT函数.

There are several ways that you can get the result, including using the PIVOT function.

您可以将聚合函数与CASE表达式一起使用:

You can use an aggregate function with a CASE expression:

select t1.aid, t1.status, t1.[group],
  max(case when t2.traitname = 'trait1' then t3.trait end) trait1,
  max(case when t2.traitname = 'trait2' then t3.trait end) trait2,
  max(case when t2.traitname = 'trait3' then t3.trait end) trait3
from table1 t1
inner join table3 t3
  on t1.aid = t3.aid
inner join table2 t2
  on t3.atid = t2.atid
group by t1.aid, t1.status, t1.[group];

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

PIVOT函数需要一个聚合函数，这就是为什么您需要使用MIN或MAX函数(因为有字符串值)的原因.

The PIVOT function requires an aggregate function this is why you would need to use either the MIN or MAX function (since you have a string value).

如果traitnames的数量有限，则可以对查询进行硬编码:

If you have a limited number of traitnames then you could hard-code the query:

select aid, status, [group],
  trait1, trait2, trait3
from
(
  select t1.aid,
    t1.status,
    t1.[group],
    t2.traitname,
    t3.trait
  from table1 t1
  inner join table3 t3
    on t1.aid = t3.aid
  inner join table2 t2
    on t3.atid = t2.atid
) d
pivot
(
  max(trait)
  for traitname in (trait1, trait2, trait3)
) piv;

请参见带演示的SQL提琴.

如果您有未知数量的值，那么您将需要考虑使用动态SQL来获得最终结果:

If you have an unknown number of values, then you will want to look at using dynamic SQL to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(traitname) 
                    from Table2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT aid, status, [group],' + @cols + ' 
            from 
            (
              select t1.aid,
                t1.status,
                t1.[group],
                t2.traitname,
                t3.trait
              from table1 t1
              inner join table3 t3
                on t1.aid = t3.aid
              inner join table2 t2
                on t3.atid = t2.atid
            ) x
            pivot 
            (
                max(trait)
                for traitname in (' + @cols + ')
            ) p '

execute sp_executesql @query;

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

这篇关于SQL Server将行分为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！