asked
Matching rows with a list of values/pattern
Hey, I'm trying to match rows based on a list of values. The list may vary in size; from one value to five values, e.g. (1, 2, 3) (1, 2) (34) (21, 4, 15, 16, 8) The rows I want to match contain five columns that I want to match with these lists: row# ability_01 ability_02 ability_03 ability_04 ability_05 [1] 1 2 3 4 5 [2] 16 17 8 9 2 [3] 21 17 8 6 2 [4] 7 15 32 1 16 With a list (1, 5) I would get row 1. With a list (8, 17) I would get rows 2 and 3. With a list (1, 2, 3, 4, 5) I would get row 1. With a list (7, 15, 1, 16) I would get row 4. I'm not sure if I'm actually trying to do something impossible. I'd like to do this with PHP but it would be better to do with SQL as pagination is already based on SQL. If you have suggestions for improving my DB structure, I would be interested to hear that as well.
declare @tbl table ( row int, ability_01 int, ability_02 int, ability_03 int, ability_04 int, ability_05 int ) declare @list varchar(10) insert into @tbl select 1, 1, 2, 3, 4, 5 union all select 2, 16, 17, 8, 9, 2 union all select 3, 21, 17, 8, 6, 2 union all select 4, 7, 15, 32, 1, 16 select @list = '8,17' ; with cte as ( select * from @tbl unpivot ( ability for col in ([ability_01], [ability_02], [ability_03], [ability_04], [ability_05]) ) p ), list as ( select val = numberval, cnt = count(*) over() from dbo.CSVTable(@list) ) select c.row from cte c inner join list l on c.ability = l.val group by c.row having count(*) = max(l.cnt) CSVTable string parsing is from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
Hey, thanks for the quick answer! How am I supposed to use this? Should this code snippet run without errors in phpMyAdmin? Edit: What do you mean by "normalize your table"?
16 People are following this question.
