在SQL Compact Edition的子查询的转置/透视结果中将结果显示为1行

问题描述

我有一个表格MonitorLocationDetails，如下图所示

I have a table MonitorLocationDetails which looks like below

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

我应该获取LocDescription的行并将其转置，就像在其他版本的sql server中一样. PIVOT在sql压缩版本中不可用.数据透视应基于子查询的结果

I should get the rows of LocDescription and transpose it like pivot in sql server other versions. PIVOT not available in sql compact versions.The Pivoting should be based on the result of a sub query

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      

即使使用DISTINCT，也要留空空间..我没有为列指定名称，因为我需要1行中的结果如下

empty spaces even with DISTINCT used.. i did not give names for columns, cause i need the result just as follows in 1 row

MP-1 descr   Main Gate descr    MP-2 descr

任何人都可以帮忙吗?

推荐答案

您已经关闭.您只需将MAX放在CASE expression之前.

You're close. You just have to put MAX before your 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

---

您还可以简化查询:

---

You could also simplify your query:

;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')

这篇关于在SQL Compact Edition的子查询的转置/透视结果中将结果显示为1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！