中子查询的转置/旋转结果中1行的显示结果

Question

我有一张桌子MonitorLocationDetails，如下所示

lid LoclColumn  LocDescription
1   MP1         MP-1 descr
2   MP2         MP-2 descr
3   MainGate    Main Gate descr 

我应该获得LocDescription的行，并像在其他版本的server中那样转换它。在sql紧凑型版本中不可用。旋转应该基于子查询的结果。

SELECT LocColumn,LocDescription 
FROM MonitorLocationDetails 
WHERE LocColumn IN ('MP1','MP2','MainGate')

这个很好用。

我到目前为止：

SELECT DISTINCT 
    (CASE WHEN P.LocColumn = 'MP1' THEN P.LocDescription ELSE '' END), 
    (CASE WHEN P.LocColumn = 'MainGate' THEN P.LocDescription ELSE '' END), 
    (CASE WHEN P.LocColumn = 'MP2' THEN P.LocDescription ELSE '' END) 
FROM (
    SELECT LocColumn,LocDescription 
    FROM MonitorLocationDetails 
    WHERE LocColumn IN ('MP1','MP2','MainGate')
) P

产生的结果：

column1     column2     column3
                        MP-2 descr
            Main Gate desc  
MP-1 descr      

空旷的空间，即使有独特的用途。我没有给列命名，因为我需要如下1行的结果

MP-1 descr   Main Gate descr    MP-2 descr

有谁能帮忙吗？

Answer 1

你离我很近。你只要把MAX放在CASE expression前面就行了。

SELECT 
    MAX(CASE WHEN P.LocColumn = 'MP1' THEN P.LocDescription ELSE '' END), 
    MAX(CASE WHEN P.LocColumn = 'MainGate' THEN P.LocDescription ELSE '' END), 
    MAX(CASE WHEN P.LocColumn = 'MP2' THEN P.LocDescription ELSE '' END) 
FROM (
    SELECT LocColumn,LocDescription 
    FROM MonitorLocationDetails 
    WHERE LocColumn IN ('MP1','MP2','MainGate')
) P

您还可以简化查询：

;WITH MonitorLocationDetails(lid, LocColumn, LocDescription) AS(
    SELECT 1, 'MP1', 'MP-1 Descr' UNION ALL
    SELECT 2, 'MP2', 'MP-2 Descr' UNION ALL
    SELECT 3, 'MainGate', 'MainGate Descr'
)
SELECT
    MAX(CASE WHEN LocColumn = 'MP1' THEN LocDescription ELSE '' END), 
    MAX(CASE WHEN LocColumn = 'MainGate' THEN LocDescription ELSE '' END), 
    MAX(CASE WHEN LocColumn = 'MP2' THEN LocDescription ELSE '' END) 
FROM MonitorLocationDetails 
WHERE LocColumn IN ('MP1','MP2','MainGate')