Author |
Topic |
micnie_2020
Posting Yak Master
232 Posts |
Posted - 2013-06-14 : 11:38:00
|
Hi All,I have a table contain:ItemIndex Item 0 email1 LIKE2 mic3 job_title4 LIKE5 IT6 age7 =8 45Notes: (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 memberswhere email like '%mic%' and job_title like 'IT' and age=45Please advise.Thank you.Regards,Micheale |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-14 : 12:53:02
|
Using Pivot but would need to be utilized via dynamic sqlDeclare @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 @queryCheersMIK |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 15:16:48
|
so is it always wildcard search for 1st, string for second and integer for third in each of the groups?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|