Q:

Want to convert Rows to columns in sql

I would like to convert a table rows to columns. I have tried to do with pivot table by below query but no luck.
select Percentage, SGST, CGST
from
(
  select 
    * 
  from #Test 
) d
pivot
(
 max(Amount)
 for Name in (SGST, CGST)
) piv

Here my table structure and query.
create table #Test(Name varchar(10),Percentage decimal(10,2),Amount decimal(10,2))

insert into #Test values ('SGST',1.5,1008351.12)
insert into #Test values ('SGST',9,3059686.27)
insert into #Test values ('CGST',1.5,1008351.12)
insert into #Test values ('CGST',9,3059686.27)

select * from #Test

Current Out Put :
--------------------------------
Name    SGST    Amount
SGST    1.50    1008351.12
SGST    9.00    3059686.27
CGST    1.50    1008351.12
CGST    9.00    3059686.27

Expected Out Put :
--------------------------------
CGST   CGSTValue     SGST   SGST Value
1.50   1008351.12    1.50   1008351.12
9.00   3059686.27    9.00   3059686.27

Thanks in Advance !

A:

You can do this with conditional aggregation:
select sgst, sum(case when name = 'SGST' then amount end) as sgst_amount,
       sum(case when name = 'CGST' then amount end) as cgst_amount
from test t
group by sgst;

