Convert multiple rows into one row and using IN keyword problem - SQL Server

Posted by Gopal_nivas under Sql Server on 8/12/2011 | Points: 10 | Views : 1579 | Status : [Member] | Replies : 1
hi..

i want to Convert multiple rows into one row - SQL Server .

i have used
DECLARE @str varchar(4000)
SET @str = (SELECT MULITPLE_TITLEIDS + ',' FROM Titles WHERE FILE_TYPE='PDF' FOR XML PATH(''))
SET @str = SUBSTRING(@str,1,LEN(@str)-1)
SELECT @str as MulitpleIDs

it gives the multipleid's in a single row

now i want to fetch the id matched values from another table i wrote query like this
SELECT * FROM PRODUCTS WHERE ID IN(@str)

now it gives the error message as
"Error converting data type varchar to bigint."

but i want to get id values from my product table.

how to do this..

need ur suggetsions with examples
regards
gopal.s




Responses

Posted by: PandianS on: 8/14/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Try this...
DECLARE @str varchar(4000) 

SET @str = (SELECT Phone1 + ',' FROM Tb_Table1 where id in(1,3) FOR XML PATH(''))
SET @str = SUBSTRING(@str,1,LEN(@str)-1)

SELECT * FROM PRODUCTS WHERE
CharIndex(Cast(ID as varchar) + ',', @str+',',0) >0
Note: If you want to use this expression in "IN" clause then you can wrire a simple Table Valued Function to split the expression and return as Table data and you can use it in "IN" clause..


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Login to post response