Is there a way to achieve below using pivot or something else in T-SQL?

I’m trying to achieve below using pivot in T-SQL:

brand a	revenue	brand b	revenue`	brand c	revenue
branch 1	20,000	branch 6	9000	branch 9	11000
branch 3	15000	branch 5	2000	branch 6	8000
branch 9	10000	branch 10	1500	branch 4	5000

However I’m getting this result:

brand a	brand b	brand c	branch
20000	null	null	branch 1
15000	null	null	branch 3
10000	null	11000	branch 9
null	9000	8000	branch 6
null	2000	null	branch 5
null	1500	null	branch 10

I’ve used this query

select
    brand a, 
    brand b,
    brand c,
    Branch
from
    (select
         r.Branch,
         r.Revenue,
         r.brand
     from 
         (--data from sales table
          select 
              branch, sum(revenue) Revenue, p.brand, 
              Row_number() over (partition by (p.brand) order by sum(revenue) desc) [rank] 
          from sales s
          join Product p on s.prodid = p.prodid
          join branch b on s.Branch = b.branch
          where p.brand in ('brand a', ' brand b', ' brand c')
            and time between @startdate and @enddate
          group by branch, p.brand) r
     where 
         r.rank <= 3) p1
pivot
    (max(revenue)
         for brand in (`brand a`, `brand b`, `brand c`)
    ) piv

Answer

Just use conditional aggregation:

select max(case when brand = 'brand a' then branch end) as branch_a,
       sum(case when brand = 'brand a' then revenue end) as revenue_a,
       max(case when brand = 'brand b' then branch end) as branch_b,
       sum(case when brand = 'brand b' then revenue end) as revenue_b,
       max(case when brand = 'brand c' then branch end) as branch_c,
       sum(case when brand = 'brand c' then revenue end) as revenue_c
from (select b.branch, sum(revenue) as Revenue, p.brand, 
             row_number() over (partition by p.brand order by 
      sum(revenue) desc) as seqnum
      from sales s join
           Product p
           on s.prodid = p.prodid join
           branch b
           on s.Branch = b.branch
      where p.brand in ('brand a',' brand b',' brand c') and
            time between @startdate and @enddate
      group by branch, p.brand
     ) pb
group by seqnum;