Group data by Value Range SQL Server

Question

I used "CASE WHEN [TRONG LUONG]< 10000 THEN '<10KG' ELSE '>10KG' END AS "TRONGLUONG" " in my query and here is my result.

NGAY GUI ---MA TINH PHAT--- TRONGLUONG--- SANLUONG

01/03/2022 --- BGG --- <10KG ---5534

01/03/2022 --- BGG --- >10KG --- 263

It's ok but how can I do to get this result? (like Pivot Table in Excel)

NGAY GUI--- MA TINH PHAT ---- SANLUONG<10kg --- SANLUONG<10kg

01/03/2022 --- BGG --- 5534 --- 263

01/03/2022 --- BKN --- 706 --- 34

Answer 1

select pvt.*
from
(
    select d.dtDate,d.category,d.sanloung, 
    case when d.weight<10000 then 'Under 10kg' else 'Over 10kg' end as TRONGLUONG
    from tblDummy as d
) as q
pivot
( 
    max(q.sanloung) for q.TRONGLUONG in ([Under 10kg],[over 10kg])
) 
as pvt