June 5, 2008 at 7:31 am
Hello I would like to create a comma delimtted list of a column in a table to use in an 'IN' clause. I can create the comma delimtted list; however, it comes out as one long string so when I use in my WHERE statement no records are returned. I'd like to enclose each element of my list in single quotes so I can use it in my query. Here is a mock table with the code I have so far. Thank you in advance for your help.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#table1') IS NOT NULL
DROP TABLE #table1
create table #table1
(somedata varchar(max))
insert into #table1 (somedata)
select 'data1' union all
select 'data2' union all
select 'data3' union all
select 'data4' union all
select 'data5' union all
select 'data6'
--select * from #table1
DECLARE @List varchar(2000)
SELECT @List = COALESCE(@List + ',', '') + Cast(somedata As varchar(5))
FROM [#table1]
SELECT @List As 'List'
--From this I get the following result:
@list = data1,data2,data3,data4,data5,data6
I'd actually like to get the following result:
@list = 'data1','data2','data3','data4','data5','data6'
so I can use in an 'IN' clause ie:
select * from table2
where
col2 IN (@list)
June 5, 2008 at 7:39 am
SELECT @List = COALESCE(@List + ',', '') + '''' + Cast(somedata As varchar(5)) + ''''
June 5, 2008 at 7:44 am
Change the SELECT to ...
SELECT @List = COALESCE(@List + ',', '') + '''' + Cast(somedata As varchar(5)) + ''''
PS. I never knew COALESCE could be used like this to create strings from records. Thanks.
June 5, 2008 at 8:00 am
Thanks Ken, worked perfectly. I had actually tried that myself but didnt use enough quotes. Lesson learned.
June 5, 2008 at 8:58 am
Hi Ken, just a follow up in case other readers want to use this. For the @List variable to work in a where clause I had to create a dynamic query, here is the final product:
DECLARE @List varchar(max), @dynamic varchar(max);
SELECT
@List = COALESCE(@List + ',', '') + '''' + Cast(Column1 As varchar(100)) + ''''
FROM Table1
set @dynamic = 'select * from Table2 where column2 in ('
set @dynamic = @dynamic + @list
exec (@dynamic)
June 5, 2008 at 11:32 am
Why aren't you just using the IN with a select statement - or with a join?
SELECT * FROM MyTable WHERE MyValue IN (SELECT MyValue FROM MyList)
SELECT T.* FROM MyTable T INNER JOIN MyList L ON T.MyValue = L.MyValue
Passing a parameter list as a delimited string and using dynamic SQL is typically a bad idea. You can run into issues with the IN becoming too long and causing performance issues. If you are passing parameters between procedures, there are better solutions also.
June 5, 2008 at 12:21 pm
Good point Michael, I'll definiately look into your suggestion.
June 5, 2008 at 1:08 pm
Yes, the coalesce function, used that way, will give you the list you want, but you can't use the list the way you mentioned in the original post.
create table #t (
ID char(1) primary key)
insert into #t (id)
select 1
union all select 2
union all select 3
union all select 4
declare @List varchar(max)
select @list = '''1'',''2'',''3'''
select *
from #t
where id in (@list)
Will give you zero results. Not because of a syntax error, but because string variables don't work that way in "In" statements.
What you should really do, in this case, if you want to use "In", is:
select * from table2
where
col2 IN (select somedate from #table1)
A) It gets rid of an unnecessary step, and (B) it works.
If you are starting out with a comma-delimited list and need to use it in an "In" statement, you first need to parse it out. There are several string parser scripts on the scripts portion of this site - make sure you grab one of the ones that uses a Numbers or Tally table, not one of the ones that uses a cursor or while loop.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply