November 7, 2012 at 1:38 pm
Hello
I have one requirement
please help me
create table #x1
(ano int,
dept varchar(30))
insert into #x1 values(1,'A1,A3,A5,A7')
insert into #x1 values(2,'A1,A2,A3,A4')
insert into #x1 values(3,'A2,A5,A9,A10')
insert into #x1 values(4,'A3,A10,A7')
insert into #x1 values(5,'A6,A7')
If i filter to A1, A5 and A6 then
desired output will be
ano
1
2
3
5
November 7, 2012 at 3:05 pm
Tempting to just say:
select ano from #x1 where ano <> 4
order by ano
but there must be some rule involving the 'dept' column in play here. What is the rule you need the SQL to apply?
November 7, 2012 at 3:06 pm
are you sure there isnt anymore to your requirements? looks a bit too simple to me
select ano from #x1
where ano!=4
order by ano
***The first step is always the hardest *******
November 7, 2012 at 3:17 pm
David Webb-200187 (11/7/2012)
Tempting to just say:select ano from #x1 where ano <> 4
order by ano
but there must be some rule involving the 'dept' column in play here. What is the rule you need the SQL to apply?
sorry I missed this
If i filter to A1, A5 and A6 then
desired output will be
ano
1
2
3
5
November 7, 2012 at 3:27 pm
To Whoever Replyed,
This is sample of data,actual table has 1 million row.
Query should be something like
select ano from #x1 where Dept in ('A1','A5','A6')
And Output should be the "list of ano" where the comma seperated table data(Dept) match with comma seperated data of where clause.
November 7, 2012 at 3:27 pm
OK,
select ano from #x1 where dept like '%a1%' or dept like '%a5%' or dept like '%a6%'
order by ano
would do it, but I suspect there is more...
Will these 3 conditions be entered as arguments to a stored proc, by any chance? If so, will they be entered as 3 separate arguments or as one string that will need to be separated?
November 7, 2012 at 3:36 pm
You are rite David,
Thanks for reply
I have to replace one very complex table function join condition llisted as below,
{ JOIN TABLE A B C query part }
Where XYZ= XYZ
AND (@Dept IS NULL OR C.Dept IN (SELECT DEPT FROM dbo.f_BuildTable(',',@Dept)))
Where F_BuildTable generate the value of @Dept from
('A1,A5,A7') to fit into in clause ('A1','A5','A7').
meaning use is passing me as one string with comma 'A1,A5,A7' but I already have function to conver it into ('A1','A5','A7').
Question is .. How would I do same for the table value(Dept col) which is like
the 'A1,A2,A3,A4' and compair them and fit into the place of abovemention stmt.
November 7, 2012 at 4:39 pm
OK, I'd do it slightly differently. Here's a link to the famous 8k splitter.
http://qa.sqlservercentral.com/articles/Tally+Table/72993/
And I'd probably use it like:
drop table #x1
create table #x1
(ano int,
dept varchar(30))
insert into #x1 values(1,'A1,A3,A5,A7')
insert into #x1 values(2,'A1,A2,A3,A4')
insert into #x1 values(3,'A2,A5,A9,A10')
insert into #x1 values(4,'A3,A10,A7')
insert into #x1 values(5,'A6,A7')
declare @dept nvarchar(200)
set @dept = 'A5,A7'
select distinct(ano) from #x1 x
cross apply [DelimitedSplit8K](x.dept,',') split
where split.item in (select item from [DelimitedSplit8K](@dept,','))
BTW, storing things you have to match to in delimited strings is hardly ever a good idea. If you can change the design to break these out into a separate table, this all becomes MUCH simpler.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply