将列名称转置为行并将值放在单独的列中 [英] Transpose column names to rows and put values in seperate column

问题描述

我有下表，您也可以在 SQL小提琴中找到

I have the following table which you can also find in the SQL fiddle here:

CREATE TABLE Flows (
    Product TEXT,
    Inbound_Date DATE,
    Outbound_Date DATE,
    Return_Date DATE,
    Quantity VARCHAR(255)
);

INSERT INTO Flows
(Product, Inbound_Date, Outbound_Date, Return_Date, Quantity)
VALUES 

("Product A", "2019-01-01", NULL, NULL, "400"),
("Product A", NULL, "2019-05-08", NULL, "200"),
("Product A", NULL, NULL, "2019-06-25", "600"),

("Product B", "2019-03-08", NULL, NULL, "380"),
("Product B", NULL, "2019-03-15", NULL, "120"),
("Product B", NULL, NULL, "2019-04-17", "610");

我使用以下SQL从表中获取值:

I use the following SQL to get the values from the table:

SELECT Product, Inbound_Date, Outbound_Date, Return_Date, sum(Quantity)
FROM Flows
GROUP BY 1,2,3,4;

到目前为止，所有这些都工作正常.

All this works fine so far.

但是，现在我想要实现将日期显示在名为 FLow_Date 的一列中.
结果应如下所示:

However, now I want to achieve that the dates are displayed in one column called FLow_Date.
The result should look like this:

Product          Date_Type            Flow_Date
Product A        Inbound_Date        2019-01-01
Product A        Oubound_Date        2019-05-08
Product A        Return_Date         2019-06-25
Product B        Inbound_Date        2019-03-08
Product B        Outbound_Date       2019-03-15
Product B        Return_Date         2019-04-17

我需要在代码中进行哪些更改才能使其正常工作?

What do I need to change in my code to make it work?

推荐答案

我认为您只需要 case 表达式:

I think you just need case expressions:

select f.product,
       (case when inbound_date is not null then 'inbound_date' 
             when outbound_date is not null then 'outbound_date'
             when return_date is not null then 'return_date'
        end) date_type,
       (case when inbound_date is not null then inbound_date
             when outbound_date is not null then outbound_date
             when return_date is not null then return_date
        end) as flow_date
from flows f;

如果每行可以有多个非 值，则会变得更加复杂.

This becomes more complicated if you can have multiple non-NULL values per row.

在这种情况下，全部联合是一种简单的方法

In that case, union all is a simple enough approach

select fd.*
from ((select f.product, 'inbound_date' as date_type, inbound_date as flow_date
       from flows f
      ) union all
      (select f.product, 'outbound_date' as date_type, outbound_date as flow_date
       from flows f
      ) union all
      (select f.product, 'return_date' as date_type, return_date as flow_date
       from flows f
      )
     ) fd
where flow_date is not null;

这不一定是最有效的方法(在这种情况下)，但是效果很好.如果 flows 实际上是一个视图，则可能会首选替代方法.

This is not necessarily the most efficient method (in this case), but it works well. If flows is really a view, then alternatives might be preferred.

这篇关于将列名称转置为行并将值放在单独的列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！