Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to use row_number if I used COALESCE

Author  Topic 

programer
Posting Yak Master

221 Posts

Posted - 2013-09-27 : 07:49:44


DECLARE @strValues varchar(100)
SELECT @strValues = COALESCE(@strValues+',', '') + CONVERT(varchar,id)
FROM tbl_BetSlipEvents
where id IN (175,205)
select @strValues

I need to used row_number where =1,2,3

Please help

programer
Posting Yak Master

221 Posts

Posted - 2013-09-27 : 08:20:29
I tried:

DECLARE @strValues varchar(100)
SELECT @strValues = COALESCE(@strValues+',', '') + CONVERT(varchar,ID)
FROM (Select row_number() over(order by id) as Number from tbl_BetSlipEvents) as a
Where a.Number IN (1,2)
select @strValues

returns
Msg 207, Level 16, State 1, Line 2
Invalid column name 'ID'.

quote:
Originally posted by programer



DECLARE @strValues varchar(100)
SELECT @strValues = COALESCE(@strValues+',', '') + CONVERT(varchar,id)
FROM tbl_BetSlipEvents
where id IN (175,205)
select @strValues

I need to used row_number where =1,2,3

Please help


Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 08:24:19
Add id also as a column in the inner query:
DECLARE @strValues varchar(100)
SELECT @strValues = COALESCE(@strValues+',', '') + CONVERT(varchar,ID)
FROM (Select row_number() over(order by id) as Number, ID from tbl_BetSlipEvents) as a
Where a.Number IN (1,2)
select @strValues
Go to Top of Page

programer
Posting Yak Master

221 Posts

Posted - 2013-09-27 : 09:11:14
DECLARE @strValues varchar(100)
DECLARE @BetSlipEventId varchar(100)

SELECT @strValues = COALESCE(@strValues+',', '') + CONVERT(varchar,ID)
FROM (Select row_number() over(order by id) as Number, ID from tbl_BetSlipEvents) as a
Where a.Number IN (1,2)
select @BetSlipEventId,@strValues

Insert into tbl_BetSlipSystem(BetSlipEventId,SystemBet)
select @BetSlipEventId,@strValues


Check now I need from tbl_BetSlipEvents to get in second table BetSlipSystem.BetSlipEventId


quote:
Originally posted by James K

Add id also as a column in the inner query:
DECLARE @strValues varchar(100)
SELECT @strValues = COALESCE(@strValues+',', '') + CONVERT(varchar,ID)
FROM (Select row_number() over(order by id) as Number, ID from tbl_BetSlipEvents) as a
Where a.Number IN (1,2)
select @strValues


Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-27 : 09:19:24
FYI In this case the COALESCE could be replace by ISNULL. Just another way of doing the same thing.

djj
Go to Top of Page

programer
Posting Yak Master

221 Posts

Posted - 2013-09-27 : 09:25:05
Look this:

DECLARE @strValues varchar(100)
DECLARE @BetSlipEventId varchar(100)

SELECT @strValues = COALESCE(@strValues+',', '') + CONVERT(varchar,ID)
FROM (Select row_number() over(order by id) as Number, ID,BetSlipDetailId from tbl_BetSlipEvents) as a
Where a.Number IN (1,2)
select @strValues

Insert into tbl_BetSlipSystem(BetSlipEventId,SystemBet)
select @strValues

How to insert tbl_BetSlipEvents.BetSlipDetailId to BetSlipSystem.BetSlipEventId ?



quote:
Originally posted by djj55

FYI In this case the COALESCE could be replace by ISNULL. Just another way of doing the same thing.

djj

Go to Top of Page
   

- Advertisement -