代码之家  ›  专栏  ›  技术社区  ›  Dave

多列文本的SQL Server案例表达

  •  0
  • Dave  · 技术社区  · 6 年前

    另一个 Case 表达式问题,我正在使用SQL Server 2016。

    我试图为每个具有多个客户列的属性返回一行。 如果属性有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
    
    1 回复  |  直到 6 年前
        1
  •  0
  •   sticky bit    6 年前

    从你现在的结果到你想要的结果 GROUP BY x.property 然后得到 max() 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 用于获取结果的示例数据的语句。