使用子查询以指定顺序创建列

Question

我正在从基于oracle的Crystal Reports过渡到SQL Server/Management Studio，并且在一个更大的查询中重新创建下面的输出时遇到了问题。我报告病人数据，所以我创建了一个示例表，我希望它能解释我所需要的。对于输出，我需要为每个客户id占一行和3个磨砂列，其中磨砂按照行号的顺序排列，但只有那些在(450,360)中有batter_code的磨砂。

订单：

id      line    batter_code   frosting 
234     1       450           chocolate
101     1       111           Strawberry
101     2       450           Orange
101     3       360           Cherry

客户：

id    first_name  last_name
234   Jon         Smith
101   Jane        Smith

输出：

id   Last Name  First Name  Frosting 1  Frosting 2  Frosting 3
101  Smith      Jane        Orange      Cherry
234  Smith      Jon         Chocolate   

我相信这种情况已经被询问和回答过了，但我找不到它。如果有解决方案，你愿意给我重定向吗？如果我需要澄清，请让我知道。再次感谢您的帮助。

Answer 1

您可以使用条件聚合：

select c.id, c.first_name, c.last_name,
       max(case when o.new_line = 1 then o.frosting end) as frosting_1,
       max(case when o.new_line = 2 then o.frosting end) as frosting_2,
       max(case when o.new_line = 3 then o.frosting end) as frosting_3
from customer c join
     (select o.*,
             row_number() over (partition by o.id order by o.line) as new_line
      from orders o
      where o.batter_code in (450, 360)
     ) o
     on c.id = o.id    
group by c.id, c.first_name, c.last_name;

关键是在进行聚合之前重新枚举“行”。

Answer 2

另一种选择是将PIVOT与Row_Number()配合使用

示例

Select *
 From  (
        Select A.ID
              ,A.[first_name]
              ,A.[last_name]
              ,Item = concat('Frosting ',Row_Number() over (Partition By B.ID Order by Line) )
              ,Value = frosting
         From Customer A
         Join Orders B on A.id=B.id
         Where batter_code in (450,360)
       ) src
 Pivot (max(Value) for Item in ([Frosting 1],[Frosting 2],[Frosting 3]) )pvt

返回

ID  first_name  last_name   Frosting 1  Frosting 2  Frosting 3
101 Jane        Smith       Orange      Cherry      NULL
234 Jon         Smith       chocolate   NULL        NULL