SQL Flatten，Pivot和查询 [英] SQL Flatten, Pivot, and Query

问题描述

我正在将大量数据合并在一起，但是我需要透视该数据，然后从该查询中选择我想要的字段.数据看起来像这样:

I am joining quite a bit of data together, but i need to pivot that data and then select the fields I want out of that query. The data looks a bit like this:

ID   OrderNo Product ProductID MetricID 指标       值
111 G456789 M12345 10          ; 89         机器    7             
111 G456789 M12345 10          ; 90        MachineID 2020      ;  
111 G456789 M12345 10          ; 91        Length      <1951      

ID    OrderNo Product ProductID MetricID Metric        value
111 G456789 M12345 10           89          Machine    7       
111 G456789 M12345 10           90          MachineID 2020       
111 G456789 M12345 10           91          Length      1951       

上面的结果集可以具有任意数量的指标(50个甚至100个或更多).该系统是可配置的，因此他们可以添加指标，这意味着我无法真正硬编码"任何会使SQL无效的内容.

The resultset above could have any number of Metrics (50 or even 100 or more). The system is configurable such that they can add metrics, which means I can't really 'hard-code' anything that would invalidate the SQL.

当ID不同时(如上文111中所述)，这将是一行. OrderNo，Product和ProductID也将相同，但是ID是关键点.

When the ID is distinct (as in 111 above), this would be a single row. The OrderNo, Product, and ProductID would also be the same, but ID is the key to the pivot.

这是透视结果集的处理方式:

This is how the Pivoted result set needs to look after:

ID   OrderNo Product ProductID 机器                     ;   MachineID 长度
111 G456789 M12345 10          ; 7               > 2020       1951   

ID    OrderNo Product ProductID Machine        MachineIDLength
111 G456789 M12345 10           7                   2020        1951   

由于可以有任意数量的指标，因此我希望它们全部在该行中.

Since there could be any number of metrics, I would want them all in that row.

现在-一切都说完了...我将只想在该结果集中查询所需的变量.因此，有可能(在上面的示例中)我只查询ID，产品，机器和长度.

NOW - After I have that all said and done... I will want to query out that resultset for ONLY the variables I need. So, it is possible (in the example above) that I would only query out ID, Product, Machine, and Length.

我敢肯定这是可以做到的.一直玩了好几个小时试图弄清楚:(

I'm sure this can be done.. just confused how! Been playing around for hours and hours trying to figure it out :(

推荐答案

尝试在聚合中使用case语句，如下所示:

Try using a case statement inside an aggregation, something like this:

SELECT ID,OrderNo, Product, ProductID,
MAX(CASE WHEN Metric='Machine' THEN value END) Machine,
MAX(CASE WHEN Metric='MachineID' THEN value END) MachineID,
MAX(CASE WHEN Metric='Length' THEN value END) Length
FROM Table1
GROUP BY ID,OrderNo, Product, ProductID

这篇关于SQL Flatten，Pivot和查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！