将具有相同 ID 的行组转置到另一个表中

问题描述

我有一个表 Demo1，其中有多个类似 DIM_KEY 的多个不同 ATTR_NAME(属性名称).我想显示一个表，其中包含 DIM_KEY、UPC、DAIRY_CLM、KOSHER_CLM、FAT 和 CALORIES 的列，其中匹配的值是行.

这是原始表Demo1:[1]:https://imgur.com/a/KqayM1C

I have a table, Demo1, where I have multiple similar DIM_KEY's for several different ATTR_NAME's (attribute names). I want to display a table with columns for DIM_KEY, UPC, DAIRY_CLM, KOSHER_CLM, FAT, and CALORIES where the matching values are the rows.

This is the original table Demo1: [1]: https://imgur.com/a/KqayM1C

这就是我想要的样子(表:Demo2):[2]:https://imgur.com/a/nwpoHhv

This is what I want it to look like (table: Demo2): [2]: https://imgur.com/a/nwpoHhv

我已经尝试将 Demo1 中的行插入到一个空的 Demo2 表中，但这并没有像我想要的那样成功.我也无法以这种方式获得 DIM_KEY.我也尝试过使用 PIVOT 但除了错误什么也没得到.我正在使用 MySQL，但这也需要在 SSMS 中工作.如果我必须选择一个，最好是 SSMS.

I've tried inserting the rows from Demo1 into an empty Demo2 table but that didn't work out like I wanted it to. I also couldn't get the DIM_KEY in that way. I also tried using PIVOT but got nothing but errors. I am using MySQL but this also needs to work in SSMS. Preferibly SSMS if I had to choose one.

INSERT INTO Demo2 (UPC, DAIRY_CLM, KOSHER_CLM, FAT, CALORIES)
SELECT
    (CASE WHEN ATTR_NAME = 'UPC' THEN VALUE END),
    (CASE WHEN ATTR_NAME = 'DAIRY_CLM' THEN VALUE END),
    (CASE WHEN ATTR_NAME = 'KOSHER_CLM' THEN VALUE END),
    (CASE WHEN ATTR_NAME = 'FAT' THEN VALUE END),
    (CASE WHEN ATTR_NAME = 'CALORIES' THEN VALUE END)
FROM Demo1;

推荐答案

这确实是一个pivot，也叫crosstab，有时也叫transpose

This is indeed a pivot, also called a crosstab, or sometimes transpose

有些数据库有专门的工具来做这件事，其他的你必须使用分组语法.我更喜欢后者，因为它普遍适用

Some databases have dedicated facilities for doing it, others you have to use a grouping syntax. I prefer the latter because it works universally

如果有任何安慰的话，你真的很接近！

If it's any consolation, you were really close!

SELECT
    DIM_KEY,
    MAX(CASE WHEN ATTR_NAME = 'UPC' THEN VALUE END) as UPC,
    MAX(CASE WHEN ATTR_NAME = 'DAIRY_CLM' THEN VALUE END) as DAIRY_CLM,
    MAX(CASE WHEN ATTR_NAME = 'KOSHER_CLM' THEN VALUE END) as KOSHER_CLM,
    MAX(CASE WHEN ATTR_NAME = 'FAT' THEN VALUE END) as FAT,
    MAX(CASE WHEN ATTR_NAME = 'CALORIES' THEN VALUE END) as CALORIES
FROM demo
GROUP BY DIM_KEY

它是如何工作的?

好吧，如果您运行您已经拥有的非分组、无最大函数版本:

Well, if you run the non-grouped, no-max-functions version that you already had:

SELECT
    DIM_KEY,
    (CASE WHEN ATTR_NAME = 'UPC' THEN VALUE END),
    (CASE WHEN ATTR_NAME = 'DAIRY_CLM' THEN VALUE END),
    (CASE WHEN ATTR_NAME = 'KOSHER_CLM' THEN VALUE END),
    (CASE WHEN ATTR_NAME = 'FAT' THEN VALUE END),
    (CASE WHEN ATTR_NAME = 'CALORIES' THEN VALUE END)
FROM
   demo

然后你会看到你的数据变成了对角线":

Then you'll see your data become "diagonal":

3005, 123423, null, null...
3005, null,   N,    null...
3005, null,   null, Y   ...

每一列(每个dim_key)只有一个值，其余为NULL

In each column (per dim_key) there is only one value, the rest are NULL

添加 GROUP BY 和 MAX 会导致它们折叠成一行，因为 MAX() 将只返回列中的值并使所有空值消失.这是分组的固有属性，行数据不会保持在一起" - 在特定 DIM_KEY 的组内，MAX(DAIRY_CLM) 可以来自任何行，MAX(KOSHER_CLM) 可以来自任何其他行..实际上，这意味着选择单个值，丢弃空值，它们都出现在同一行..

Adding in the GROUP BY and MAX causes these to collapse into a single row because MAX() will return just the value from the column and make all the nulls disappear. It's an intrinsic property of a grouping, that the row data doesnt "stay together" - within the group of a particular DIM_KEY, the MAX(DAIRY_CLM) can come from any row, the MAX(KOSHER_CLM) can come from any other row.. In practice this means that the single values are picked, the nulls are discarded, they all appear on the same row..

..因此您的垂直数据在经过对角线后变为水平

..and thus your vertical data went horizontal, after going through the diagonal

这篇关于将具有相同 ID 的行组转置到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！