Unpivot子查询

Question

我需要将这个查询结果转换成行而不是列。使用Un透视是可能的，但我无法执行此活动，请参阅代码：

    SELECT
cast(IIF(DATEPART(HOUR, [DATA]) <= 4, DATEADD(DAY, -1, [DATA]), [DATA]) as date) as Data,
IIF(DATEPART(HOUR, [DATA]) <= 4, DATEADD(DAY, -1, [DATA]), [DATA]) AS DataHora,
SUM([FraturaContusao]) AS 'FraturaContusao',
SUM([Salpingite]) AS 'Salpingite'
FROM
(
    SELECT
        [DATA], ROMANEIO,
        IIF(( bAbaco.FRATURA_CONTUSAO - COALESCE(LAG(bAbaco.FRATURA_CONTUSAO) OVER (ORDER BY bAbaco.ID), 0)) > 0 , ( bAbaco.FRATURA_CONTUSAO - COALESCE(LAG(bAbaco.FRATURA_CONTUSAO) OVER ( ORDER BY bAbaco.ID), 0)), 0) AS 'FraturaContusao',
        IIF(( bAbaco.Salpingite - COALESCE(LAG(bAbaco.Salpingite) OVER (ORDER BY bAbaco.ID), 0)) > 0 , ( bAbaco.Salpingite - COALESCE(LAG(bAbaco.Salpingite) OVER ( ORDER BY bAbaco.ID), 0)), 0) AS 'Salpingite'
    FROM
    ContProdUIA.ABACO_DIGITAL_L2 bAbaco
    ) as x
WHERE
CONVERT(varchar, x.[DATA], 23) >= DATEADD(DAY, -16, GETDATE())
GROUP BY 
IIF(DATEPART(HOUR, [DATA]) <= 4, DATEADD(DAY, -1, [DATA]), [DATA])

这是查询的结果：

​

​

我需要将列'FraturaContusao‘和'Salpingite’转换为unpviot，如下所示：

Data| DataHora | Tipo| Qty
2020-06-23 | 2020-06-23 15:54 | FraturaContusao| 15
2020-06-23 | 2020-06-23 15:54 | Salpingite | 20

Answer 1

也许最简单的方法是使用CROSS APPLY

示例

 Select A.Data
       ,A.DataHora
       ,B.Tipo
       ,B.Qty
 From  (
         -- your orignal query here --
       ) A
 Cross Apply ( values ('FraturaContusao',FraturaContusao)
                     ,('Salpingite'     ,Salpingite)
             ) B(Tipo,Qty)