T-SQL. How do I implement multiple rows with rank to a single row transformation

Question

SQL Server:

I have a query that returns the table of the most popular goods delivered to customers:

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

This data means that CustomerId = 1 most popular goods are: TV, PC, Games, Drinks

How may I transform this data to TOP-3 good of every customer like that:

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

I guess I may use multiple select/join using rank = 1, rank = 2, rank = 3. Is there more efficient approach?

Possible duplicate of [Understanding PIVOT function in T-SQL](https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) — Alex, May 01 '19 at 04:10
Not sure why, you got a down vote. What you are after is called `PIVOT`. There is plenty of information on how to get it working. — Alex, May 01 '19 at 04:11
@Alex Probably due to no attempt AKA does not show any research effort. — Dale K, May 01 '19 at 04:23

score 1 · Answer 1 · answered May 01 '19 at 04:58

We can use case with group by statement.

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

Note - In above solution , I am assuming that there will be no two rows with same rank for single customerId.

mkRabbani · Answer 2 · 2019-05-01T05:45:16.530

This following script will provide your required output but I am not sure this is the best practice or not. But it will give some scope for thoughts for sure-

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

T-SQL. How do I implement multiple rows with rank to a single row transformation

2 Answers2