sql - 组合多行以创建具有相同 ID 的新行

我正在尝试将表格格式修改为新格式，这将有助于我处理我正在处理的新 .Net 应用程序。更改很简单，但我无法解决。

以下是当前格式的示例:

 RecoNumber   Position  Type            Length

 NO VON-01    Top       Spiralbelt      44.27274
 NO VON-01    Bottom    Venta Trac      13.10656
 ACMHAPA-01   Top       Rhino Hyde-B    28.12
 ACMHAPA-01   Lagging   OPL-35          0
 ADVGRMI-01   Top       Spirabelt 1.05  35.40905
 ADVGRMI-01   Bottom    Venta Trac-B    13.05474
 ADVGRMI-01   Lagging   BMD-6           0

这是格式最终应该如何的示例:

RecoNumber   TopType         BtmType       LaggingType   TopLength   BtmLength   LaggingLength

NO VON-01    Spiralbelt      Venta Trac    NULL          44.27274    13.10656    NULL
ACMHAPA-01   Rhino Hyde-B    NULL          OPL-35        28.12       NULL        0
ADVGRMI-01   Spirabelt 1.05  Venta Trac-B  BMD-6         35.40905    13.05474    0

我曾尝试使用 Group By 或 Row_Number() 函数来合并，但我无法让它工作。到目前为止，我最接近的尝试是隔离每个单元格，但我无法将它们全部合并为一行。

这是我到目前为止的位置:

SELECT  RecoNumber,
        CASE WHEN (Position = 'Top') THEN Type ELSE NULL END as TopType,
        CASE WHEN (Position = 'Top') THEN Length ELSE NULL END as TopLength,
        CASE WHEN (Position = 'Bottom') THEN Type ELSE NULL END as BtmType,
        CASE WHEN (Position = 'Bottom') THEN Length ELSE NULL END as BtmLength,
        CASE WHEN (Position = 'Lagging') THEN Type ELSE NULL END AS LaggingType,
        CASE WHEN (Position = 'Lagging') THEN Length ELSE NULL END as LaggingLength
FROM Workbook2014_Test.dbo.RecoBeltsOld

result:

RecoNumber  TopType        TopLength  BtmType      BtmLength    LaggingType    LaggingLength

NO VON-01   Spiralbelt     44.27274   NULL         NULL         NULL           NULL
NO VON-01   NULL           NULL       Venta Trac   13.10656     NULL           NULL
ACMHAPA-01  Rhino Hyde-B   28.12      NULL         NULL         NULL           NULL
ACMHAPA-01  NULL           NULL       NULL         NULL OPL-35  0
ADVGRMI-01  Spirabelt 1.05 35.40905   NULL         NULL         NULL           NULL
ADVGRMI-01  NULL           NULL       Venta Trac-B 13.05474     NULL           NULL
ADVGRMI-01  NULL           NULL       NULL         NULL         BMD-6          0

感谢您的任何提示，西蒙

最佳答案

您可以使用 group by 将它们全部放在同一 RecoNumber 行上。

SELECT  RecoNumber,
        max(CASE WHEN (Position = 'Top') THEN Type ELSE NULL END) as TopType,
        max(CASE WHEN (Position = 'Top') THEN Length ELSE NULL END) as TopLength,
        max(CASE WHEN (Position = 'Bottom') THEN Type ELSE NULL END) as BtmType,
        max(CASE WHEN (Position = 'Bottom') THEN Length ELSE NULL END) as BtmLength,
        max(CASE WHEN (Position = 'Lagging') THEN Type ELSE NULL END) AS LaggingType,
        max(CASE WHEN (Position = 'Lagging') THEN Length ELSE NULL END) as LaggingLength
FROM Workbook2014_Test.dbo.RecoBeltsOld
group by RecoNumber

关于sql - 组合多行以创建具有相同 ID 的新行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30984171/