Looking for a query match in 1 of multiple columns

Question

Looking for a query match in 1 of multiple columns

tony-1020600

Old Hand

Points: 360
More actions
April 28, 2009 at 6:33 am

#384457

Hi,
I have a data table that has multiple columns that have similar data, e.g. skills of a worker.
There are 8 columns and there is no prefined order in which these columns must be populated. For example being a referee could find 3 matches in the table, but the skill of referee might be in column 1 for worker1, column 3 for worker 2 and column 8 for worker 3.
How can I script a query that would find only those 3 workers out of my workforce without having to retrieve the whole table and then searching each item individually?
My guess is it would be something similar to
SELECT WORKER_NAME FROM WORKERS WHERE SKILL1 OR SKILL2 OR ..... OR SKILL8 = 'REFEREE'
Thanks
Tony
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

tony (4/28/2009)
Hi,
I have a data table that has multiple columns that have similar data, e.g. skills of a worker.
There are 8 columns and there is no prefined order in which these columns must be populated. For example being a referee could find 3 matches in the table, but the skill of referee might be in column 1 for worker1, column 3 for worker 2 and column 8 for worker 3.
How can I script a query that would find only those 3 workers out of my workforce without having to retrieve the whole table and then searching each item individually?
My guess is it would be something similar to
SELECT WORKER_NAME FROM WORKERS WHERE SKILL1 OR SKILL2 OR ..... OR SKILL8 = 'REFEREE'
Thanks
Tony

Why cant u use select worker_name from workers where skill like 'referee'

tony-1020600 Old Hand Points: 360 More actions · Answer 2

How does that work for each column?

I tried it and was told that the column name didnt exist.....

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Yeah you were close.

it should be

SELECT WORKER_NAME FROM WORKERS WHERE SKILL1 = 'REFEREE' or SKILL2 = 'REFEREE' etc.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

tony (4/28/2009)
I have a data table that has multiple columns that have similar data, e.g. skills of a worker.

If changing the table is an option, queries like this will be a lot, lot easier if it was properly normalised. (ie skills in a separate table, one per row)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 5

Hi,

like Gail already stated, normalization of the table would solve the issue in general by using "the easy way"... 🙂

Your database design has the following "disadvantages": Where do you store the 9th skill of a worker and why do you keep 8 skills available if most of the workers probably have less than 8?

If modifying the table structure is an option for you, you could add a computed column where you add up all your 8 skill columns, separated by a character not used within a skill. This would allow you to keep the current logic for filling the table (other than the highly recommended normalization of the table, or at least, without using an insert trigger to "fake" normalization...).

Then you could query the computed column for example with "where TotalSkills like %|referee%" (assuming the pipe as a separator, also added before the first skill).

But this would not help you to get a normalized/flexible database...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

hayzer Say Hey Kid Points: 713 More actions · Answer 6

You can use this code TEMPORARILY

declare @abc table

(

Applicant char(20),

Skills1 char (20),

Skills2 char (20),

Skills3 char (20),

Skills4 char (20),

Skills5 char (20),

Skills6 char (20)

)

insert into @abc

select 'Jones', 'Act', 'Cry', 'Eat', 'Fly', 'Walk', 'Run' union all

select 'Jane', 'Bake', 'Fake', 'Take', 'Make', 'Wake', 'Sake' union all

select 'Donald', 'Hold', 'Mold', 'Cold', 'Sold', 'Fold', 'Told'

select * from

(select Applicant, Skills1 as Skills from @abc union all

select Applicant, Skills2 from @abc union all

select Applicant, Skills3 from @abc union all

select Applicant, Skills4 from @abc union all

select Applicant, Skills5 from @abc union all

select Applicant, Skills6 from @abc) A

where Skills = 'Fake'

While this code should do the trick, it is recommended that you review the normalization rule... it would be much simplier if the tables are normalized...

http://www.datamodel.org/NormalizationRules.html

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

I agree about normalising.:)

However if you have to do it this way, you can:

Use Unpivot

Or

Use a where clause like:

WHERE 'CODING' IN (skill1,skill2,skill3 etc...)

I make no claims for performance or scalability!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);