CODEWITHSUNDEEP
sql-servert-sql
user149691
user149691

Reputation: 607

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

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?

Upvotes: 0

Views: 172

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

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

Upvotes: 0

DatabaseCoder
DatabaseCoder

Reputation: 2032

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.

Upvotes: 1

Related Questions