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 : 2512 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts