sql-server - 将文本转换为多列的 SQL Server 大小写表达式

Question

另一个Case表达问题，我使用的是 SQL Server 2016。

我正在尝试为具有多个客户列的每个属性返回 1 行。如果该物业有 1 个客户，则第 2 和第 3 个客户列应为空。我已经使用同一个表的连接来返回带有RANK属性的客户，但我的结果为每个属性返回多行。

期望的输出：

Prop    Cust1   Cust2   Cust3
-------------------------------
PropA   Fred    John    NULL
PropB   Ang     Jo      Allan

询问：

SELECT DISTINCT
    x.Property as Prop,
    CASE WHEN x.RANK = 1 THEN x.Customer END AS Cust1,
    CASE WHEN x.RANK = 2 THEN x.Customer END AS Cust2,
    CASE WHEN x.RANK = 3 THEN x.Customer END AS Cust3
FROM 
    tbl_CustbyProperty c
LEFT JOIN
    (SELECT DISTINCT  
         Cust_ID, Customer, Property
         ROW_NUMBER() OVER (PARTITION BY c.Property ORDER BY c.customer) AS RANK
     FROM 
         tbl_CustbyProperty) x ON c.Cust_ID = x.Cust_ID
ORDER BY 
    y x.Property

电流输出：

Prop     Cust1   Cust2   Cust3
--------------------------------
PropA    Fred    NULL    NULL
PropA    NULL    John    NULL
PropB    Ang     NULL    NULL
PropB    NULL    Jo      NULL
PropB    NULL    NULL    Allan

标签： sql-servercase

Answer 1

要从您当前的结果到您想要的结果，您可以GROUP BY x.property得到smax()的CASE。类似于：

SELECT x.property prop,
       max(CASE 
             WHEN x.rank = 1 THEN
               x.customer
           END) cust1,
       max(CASE
             WHEN x.rank = 2 THEN
               x.customer
           END) cust2,
       max(CASE
             WHEN x.rank = 3 THEN
               x.customer
           END) cust3
       FROM tbl_custbyproperty c
            LEFT JOIN (SELECT DISTINCT
                              cust_id,
                              customer,
                              property,
                              row_number() OVER (PARTITION BY c.property
                                                 ORDER BY c.customer) rank
                              FROM tbl_custbyproperty) x
                      ON c.cust_id = x.cust_id
       GROUP BY x.property
       ORDER BY x.property;

CREATE但是，如果您编辑您的问题并将表格的陈述与INSERT您获得结果的样本数据的陈述一起发布，那么您可能会得到一个整体更好的解决方案。