SQL Search multiple columns for certain values

Question

I have a Diagnosis table like shown below (just an eg) There are 50 ICD10 columns as shown below.

+----+-------------+------+----------------+------+-----+------+-------+--------------+-------+
| ID | ICD10_Diag1 | POA1 | ICD10_Diag2    | POA2 | ... | .... | POA49 | ICD10_Diag50 | POA50 |
+----+-------------+------+----------------+------+-----+------+-------+--------------+-------+
| 1  | A234 - Flu  | Y    | B456 - Cough   | Y    |     |      | N     | A844 - Fever | N     |
+----+-------------+------+----------------+------+-----+------+-------+--------------+-------+
| 2  | A563 - Cold | Y    | I567 - Cardiac | Y    |     |      |       |              |       |
+----+-------------+------+----------------+------+-----+------+-------+--------------+-------+
| 3  | C354 - CHF  | N    |                |      |     |      |       |              |       |
+----+-------------+------+----------------+------+-----+------+-------+--------------+-------+

I'm trying to see how I can transpose this table structure so that I can perform the below operation.

Find the records which contain these Diagnosis codes(A234, F384, I567) and their corresponding POA is 'Y'. Even if a single match is found then then ignore the rest of the diagnosis even if there is a match.

SQL

Select * from myTable WHERE ('%A234%', '%F384%', '%I567%') IN (ICD10_Diag1, ICD10_Diag2, ICD10_Diag3, ICD10_Diag4)

I know this is not the right way to do it, I'm trying to find an alternative

Desired Result

1 A234-Flu 
2 I567 - Cardiac

Any chance you can normalize this table? It won't only help here, but with many other problems. — Luis Cazares, Mar 26 '19 at 19:01
I'm tempted to flag this as a duplicate. The answer to this question certainly solves the hard part of yours, if not all of it: https://stackoverflow.com/questions/47574113/sql-server-unpivot-two-columns — Tab Alleman, Mar 26 '19 at 19:13
I agree 1000000% with @LuisCazares. Writing a query here is an exercise in futility. The reason this is so difficult is because your design contains repeating groups which violates 1NF. — Sean Lange, Mar 26 '19 at 19:43

score 0 · Answer 1 · answered Mar 26 '19 at 19:47

I recommend unpivoting the data. You can do this in the query using apply, but it is better to structure the data like this:

select d.*
from diagnosis d cross apply
     (values (d.ICD10_Diag1, d.POA1),
             (d.ICD10_Diag2, d.POA2),
             . . . 
     ) v(ICD10_Diag, POA)
where v.ICD10_Diag in ('A234', 'F384', 'I567') 
      v.POA = 'Y';

Note that this could return duplicates. A simple solution is select distinct.

SQL Search multiple columns for certain values

1 Answers1