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 pass Items from a table as sql where ...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
227 Posts

Posted - 06/14/2013 :  11:38:00  Show Profile  Reply with Quote
Hi All,

I have a table contain:

ItemIndex Item
0 email
1 LIKE
2 mic

3 job_title
4 LIKE
5 IT
6 age
7 =
8 45


Notes: (3 set per each filter (0-3, 4-6, 7-9, 10-12 ...)

How can i manipulate above item(s) to be as bold section as below where .... without hardcoded?

select * from members
where email like '%mic%' and job_title like 'IT' and age=45

Please advise.

Thank you.

Regards,
Micheale

Edited by - micnie_2020 on 06/14/2013 11:39:55

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/14/2013 :  12:53:02  Show Profile  Reply with Quote
Using Pivot but would need to be utilized via dynamic sql

Declare @query varchar(8000)
set @query='select * from members where '
declare @tab table (ItemIndex int,Item varchar(10))
insert into @tab values (0,'email'),(1,'LIKE'),(2,'mic'),(3,'job_title'),(4,'LIKE'),(5,'IT'),(6,'age'),(7,'='),(8,'45')
SET @query=@query+
(SELECT [0]+' '+[1]+' ''%'+[2]+'%'' AND '+[3]+' '+[4]+' ''%'+[5]+'%'' AND '+[6]+' '+[7]+' '+
FROM (
SELECT
PK as 'Item',[0],[1],[2],[3],[4],[5],[6],[7],
FROM (
select 'Item' as Pk,ItemIndex,Item from @tab
) Src
Pivot
(
Max(Item) For ItemIndex in ([0],[1],[2],[3],[4],[5],[6],[7],)
)AS Pvt
)Query
)
Print @query

Cheers
MIK

Edited by - MIK_2008 on 06/14/2013 12:54:19
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/14/2013 :  15:16:48  Show Profile  Reply with Quote
so is it always wildcard search for 1st, string for second and integer for third in each of the groups?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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