How to Show this Data using Sql Query,. [Resolved]

Posted by Jayakumars under C# on 10/14/2016 | Points: 10 | Views : 167 | Status : [Member] [MVP] | Replies : 2
CREATE TABLE #FinalRates
(
id int primary key identity(1,1),
RateDesc nvarchar(50),
Amt decimal(18,2)
)



insert into #FinalRates values('100',200)
insert into #FinalRates values('100',300)
insert into #FinalRates values('30-50',500)
insert into #FinalRates values('30-50',250)
insert into #FinalRates values('50-80',100)
insert into #FinalRates values('50-80',300)


-- My Data This

ID RateDesc amt1
1 100 200.00
2 100 300.00
3 30-50 500.00
4 30-50 250.00
5 50-80 100.00
6 50-80 300.00


--But I need output this
ID	Rate desc	amt1	amt2
1 100 200.00 300.00
2 30-50 100.00 300.00
3 50-80 500.00 250.00


Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 10/14/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@JayaKumar Sir,
Try this

CREATE TABLE #FinalRates
(
id int primary key identity(1,1),
RateDesc nvarchar(50),
Amt decimal(18,2)
)

insert into #FinalRates values('100',200)
insert into #FinalRates values('100',300)
insert into #FinalRates values('50-80',100)
insert into #FinalRates values('50-80',300)
insert into #FinalRates values('30-50',500)
insert into #FinalRates values('30-50',250)

select
ID = ROW_NUMBER() Over(Order By (Select 1))
,RateDesc
,amt1 = max(case when seqnum = 1 then Amt end)
, amt2 = max(case when seqnum = 2 then Amt end)
from (select ft.*,
row_number() over (partition by RateDesc order by id) as seqnum
from #FinalRates ft
) fr
group by RateDesc

Drop table #FinalRates


Result
---------
ID	RateDesc	amt1	amt2
1 100 200.00 300.00
2 30-50 500.00 250.00
3 50-80 100.00 300.00


Hope that helps

N.B.~ The output that you presented is wrong ... you might have swapped the amt1 and amt2 of Id 2 and 3. Might be a typo. Please check .

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 10/14/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
please look me my insert query

My Data this


ID RateDesc amt1
1 100 200.00
2 100 300.00
3 30-50 500.00
4 30-50 250.00
5 50-80 100.00
6 50-80 300.00

i need ascending order based on id i need output this

1 100 200 300
2 30-50 500 250
3 50-80 100 300



Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response