SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to use row_number if I used COALESCE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

programer
Posting Yak Master

Slovenia
215 Posts

Posted - 09/27/2013 :  07:49:44  Show Profile  Reply with Quote


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

Slovenia
215 Posts

Posted - 09/27/2013 :  08:20:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 09/27/2013 :  08:24:19  Show Profile  Reply with Quote
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

Slovenia
215 Posts

Posted - 09/27/2013 :  09:11:14  Show Profile  Reply with Quote
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

USA
283 Posts

Posted - 09/27/2013 :  09:19:24  Show Profile  Reply with Quote
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

Slovenia
215 Posts

Posted - 09/27/2013 :  09:25:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000