How to show output using following Data [Resolved]

Posted by Jayakumars under Sql Server on 10/8/2016 | Points: 10 | Views : 1202 | Status : [Member] [MVP] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rajnilari2015 on: 10/8/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 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

@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

0	Hi Brilliant Always Rajnilari2015 Awesome Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

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

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

Kumaraspcode2009@gmail.com

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

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

@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

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

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

Kumaraspcode2009@gmail.com

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

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

@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

Latest Posts