-4

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 !

AGH
  • 353
  • 1
  • 14
  • 2
    *"I have tried my best using pivot but no luck"* Please do show us what you tried. If you don't show us, how can we help you fix the SQL you've written? Did you have a look at the many duplicates for questions like this, such as [here](https://stackoverflow.com/q/15931607/3484879) and [here](https://stackoverflow.com/q/15745042/3484879)? – Thom A Jun 20 '19 at 13:17
  • How do you know which CGST corresponds to which SGST? There's no way to define an order in there. – Luis Cazares Jun 20 '19 at 13:29
  • @Larnu Thanks for correcting me, I will definitely follow your suggestion towards. – AGH Jun 21 '19 at 05:39
  • @LuisCazares Shared data comes in big complex query but due some constraint, i could not share here. – AGH Jun 21 '19 at 05:42

1 Answers1

3

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786