How to show output using following Data [Resolved]

Posted by Jayakumars under Sql Server on 10/8/2016 | Points: 10 | Views : 211 | Status : [Member] [MVP] | Replies : 6
Hi
How to show this output any one guide me.


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 * FROM FinalRates


-- i NEED OUTPUT THIS

 RateDesc		Amount1 Amount2
100 200 300
50-80 100 300
30-50 500 250


Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 10/8/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 RateDesc,
max(case when seqnum = 1 then Amt end) as Amount1,
max(case when seqnum = 2 then Amt end) as Amount2
from (select ft.*,
row_number() over (partition by RateDesc order by id) as seqnum
from #FinalRates ft
) fr
group by RateDesc
order by case when CAST(REPLACE(Left(RateDesc,CHARINDEX('-',RateDesc)),'-','') AS INT) = 0 then CAST(RateDesc AS INT) else CAST(REPLACE(Left(RateDesc,CHARINDEX('-',RateDesc)),'-','') AS INT) end DESC

Drop table #FinalRates


Result
-----------
RateDesc	Amount1	Amount2

100 200.00 300.00
50-80 100.00 300.00
30-50 500.00 250.00




--
Thanks & Regards,
RNA Team

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

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

Up
0
Down
Hi
Brilliant Always Rajnilari2015 Awesome

Mark as Answer if its helpful to you

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

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

Up
0
Down
Hi

Rajnilari

you Query Mistake Some one .


-- My Table data Order in table id is
--ID amt

--1 100
--2 50-80
--3 30-50


--But your output shows
--1 100

--3 30-50
--2 50-80


--- I need the output based on id ascending
-- i tried asc not workong.

--can you check and update soon Most urgent

Mark as Answer if its helpful to you

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

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

Up
0
Down
@Jayakumars , check the updated one. Need numeric sort

.....................

......................
order by case when CAST(REPLACE(Left(RateDesc,CHARINDEX('-',RateDesc)),'-','') AS INT) = 0 then CAST(RateDesc AS INT) else CAST(REPLACE(Left(RateDesc,CHARINDEX('-',RateDesc)),'-','') AS INT) end DESC


--
Thanks & Regards,
RNA Team

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

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

Up
0
Down
Hello RNA team Ranjari 2015

your Query wrong not working . I have mention my Requirement below. Can you try and update Please.


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

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

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

Up
0
Down
@Jayakumars Sir, we have given you the output as what has been asked in the original question. Since you have asked now to present it in a new form , please post it in a new thread and we will do so. Please read/re-read your original question before making a remark "your Query wrong not working" . Hope that clarifies your answer.
Thank you.

--
Thanks & Regards,
RNA Team

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

Login to post response