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 值的部分.我不明白为什么我需要那个 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];
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 Fiddle with Demo.
如果您有未知数量的值，那么您将需要考虑使用动态 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 Server 将行变成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
