Select * from Mytable a
where a.FieldName = 'Accounting'
and 
(a.Value1 = 'Mentor'
 or 
a.Value2 = 'Mentor'
or
a.Value3 = 'Mentor'
)
and Exists (Select * from MyTable aa where aa.FieldName = 'Spanish' and
(aa.Value1 = 'Fluent'
or
aa.Value2 = 'Fluent'
or
aa.Value3 = 'Fluent'
))

quote:

---

Originally posted by dmoonme

Sample data

SURVEYID,FIELDID,FIELDNAME,VALUE1,VALUE2,VALUE3
20,5,Accounting,Mentor,None,NULL
20,246,Spanish,Fluent,Fluent,Fluent
21,5,Accounting,None,None,NULL
21,246,Spanish,Limited,Limited,Limited
22,5,Accounting,None,None,NULL
22,246,Spanish,Fluent,Limited,Limted

I want to get rows where someone can mentor in accounting ANDis fluent in Spanish.

---

SELECT * FROM YourTable WHERE SURVEYID IN
(
SELECT SURVEYID
FROM YourTable
GROUP BY SURVEYID
HAVING SUM(CASE WHEN FIELDNAME ='Accounting' AND ',' + COALESCE(VALUE1 + ',','') + COALESCE(VALUE2 + ',','') + COALESCE(VALUE3+',','')  LIKE '%,Mentor,%' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN FIELDNAME ='Spanish' AND  ',' + COALESCE(VALUE1 + ',','') + COALESCE(VALUE2 + ',','') + COALESCE(VALUE3+',','')  LIKE '%,Fluent,%' THEN 1 ELSE 0 END)>0)