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

Posted by Jayakumars under C# on 10/14/2016 | Points: 10 | Views : 1180 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	@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

@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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

Kumaraspcode2009@gmail.com

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

Latest Posts