T-SQL。如何实现从多行到单行的排名转换

Question

SQL Server：

我有一个查询，它返回交付给客户的最受欢迎商品的表：

CustomerId TotalPrice, Name, Rank 
===================================
1          100          TV       1
2          50           Coffee   1
1          70           PC       2
3          30           Clothes  1
1          60           Games    3
1          50           Drinks   4

这个数据意味着CustomerId =1最受欢迎的商品是:电视，个人电脑，游戏，饮料

我如何将这些数据转换为每个客户的前3名产品：

CustomerId TOP-1 TOP-1-Price TOP-2 TOP-2-Price TOP-3 TOP-3-Price 
====================================================================
    1       TV       100        PC      70      Games    60 
    2       Coffee   50        NULL     NULL    NULL     NULL
    3       Clothes  30        NULL     NULL    NULL     NULL 

我猜我可以使用多个select/join，使用rank = 1，rank = 2，rank = 3。有没有更有效的方法？

Answer 1

我们可以在group by语句中使用case。

declare @temptbl table (
CustomerId int, TotalPrice int, Name varchar(50), [Rank] int);

insert into @temptbl
          select 1,100,'TV'       ,1
union all select 2,50 ,'Coffee'   ,1
union all select 1,70 ,'PC'       ,2
union all select 3,30 ,'Clothes'  ,1
union all select 1,60 ,'Games'    ,3
union all select 1,50 ,'Drinks'   ,4

select
    CustomerId,
    top1 = MAX(CASE WHEN [Rank] = 1 THEN (Name) ELSE NULL END),
    top1_price = SUM(CASE WHEN [Rank] = 1 THEN TotalPrice ELSE NULL END),
    top2 = MAX(CASE WHEN [Rank] = 2 THEN (Name) ELSE NULL END),
    top2_price = SUM(CASE WHEN [Rank] = 2 THEN TotalPrice ELSE NULL END),
    top3 = MAX(CASE WHEN [Rank] = 3 THEN (Name) ELSE NULL END),
    top3_price = SUM(CASE WHEN [Rank] = 3 THEN TotalPrice ELSE NULL END)
from @temptbl
group by CustomerId

注意-在上面的解决方案中，我假设对于单个customerId不会有两个具有相同排名的行。

Answer 2

以下脚本将提供所需的输出，但我不确定这是否是最佳实践。但这肯定会给我们一些思考的空间--

SELECT C.A CustomerID,
MAX(C.[TOP 1]) [TOP-1],SUM(C.[TOP 1 PRICE]) [TOP-1-PRICE],
MAX(C.[TOP 2]) [TOP-2],SUM(C.[TOP 2 PRICE]) [TOP-2-PRICE],
MAX(C.[TOP 3]) [TOP-3],SUM(C.[TOP 3 PRICE]) [TOP-3-PRICE]
FROM (
    SELECT B.A, 
    CASE WHEN DR = 1 THEN B.C ELSE NULL END  AS [TOP 1],
    CASE WHEN DR = 1 THEN B.B ELSE NULL END  AS [TOP 1 PRICE],
    CASE WHEN DR = 2 THEN B.C ELSE NULL END  AS [TOP 2],
    CASE WHEN DR = 2 THEN B.B ELSE NULL END  AS [TOP 2 PRICE],
    CASE WHEN DR = 3 THEN B.C ELSE NULL END  AS [TOP 3],
    CASE WHEN DR = 3 THEN B.B ELSE NULL END  AS [TOP 3 PRICE]
    FROM (
        SELECT A,b,c, 
        DENSE_RANK() over(PARTITION BY a order by B desc) DR
        FROM (
            VALUES
            (1,100,'TV',1),
            (2,50,'Coffee',1),
            (1,70,'PC',2),
            (3,30,'Clothes',1),
            (1,60,'Games',3),
            (1,50,'Drinks',4)
        ) V (A,b,c,D)
    )B
WHERE DR <4
)C
GROUP BY C.A