CODEWITHSUNDEEP
sql-servert-sql
Lee Y.
Lee Y.

Reputation: 119

Smarter code to query the same parameter from multiple columns?

We have this very wide table that has 25 diagnosis code columns: 

diag1, diag2, … diag25.

If I want to look for diagnosis code '12345' I need to write 

diag1 ='12345' or diag2='12345' or diag3='12345'... or diag25='12345'

Is there a way to do something like this:

diag1-diag25 ='12345'

Upvotes: 2

Views: 175

Answers (5)

Eric Brandt
Eric Brandt

Reputation: 8101

Another way would be to UNPIVOT your diagnosis codes into a single column, then query the resulting data set.

For a full discussion of unpivoting options, see this question. SQL Server : Columns to Rows

With varchar diagnosis codes, BETWEEN might not behave the way you want it to, but you could dump your 70-odd codes into a temp table and join to it.

But something along these lines:

select * from 
(
  select 
    <Any Key Fields You Want To Include>,
    diagColumnName,
    diagCode
  from yourtable
  unpivot
  (
    diagCode
    for diagColumnName in (diag1, diag2....diag25)
  ) as unpiv
) as resultSet
join #tempCodes as t
  on t.diagCode = resultSet.diagCode

Edit: added the join/temp table based on comments.

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You might call the generic abilities of XML to your rescue. Not very fast with may rows, but very mighty:

(credits to iamdave for the mockup)

declare @t table(id int,diag1 int,diag2 int,diag3 int,diag4 int,diag5 int,diag6 int,diag7 int,diag8 int,diag9 int,diag10 int);
insert into @t (id, diag5) values(1,12345);
insert into @t (id, diag3) values(2,123);
insert into @t (id, diag8, diag1) values(3,123,12345);
insert into @t (id, diag9, diag2) values(4,345,678);

WITH CreateXML AS
(
    SELECT * 
          ,(
            SELECT *
            FROM @t t2
            WHERE t1.id=t2.id
            FOR XML PATH('codes'),TYPE
           ).query('/codes/*[substring(local-name(),1,4)="diag"]') AllCodes
     FROM @t t1 
)
SELECT * 
FROM CreateXML
WHERE AllCodes.exist('/*[. cast as xs:int? >=12345 and . cast as xs:int? <=12349]')=1;

The query will use a SELECT * to create an XML of all columns. The .query() will pick all elements starting with "diag". Maybe you have to adapt this to your needs.

The .exist() will check, if any value within these elements is between your borders. One match is enough.

Good Luck!

Upvotes: 3

GuidoG
GuidoG

Reputation: 12039

Actually all these diagxx columns should be in a seperate table. So your design should be altered.

If you cannot do that then you can make your query easier en cleaner by using an IN in your where clause.

declare @d table (diag1 varchar(5), diag2 varchar(5), diag3 varchar(5))
insert into @d values ('12345', '23456', '34567'),
                      ('45678', '12345', '56789'),
                      ('45678', '85236', '56789')
select * 
from   @d
where  '12345' in (diag1, diag2, diag3)

This will return 

diag1   diag2   diag3   
-----   -----   -----   
12345   23456   34567   
45678   12345   56789

This is as close to your desired syntax as you are going to get

Upvotes: 1

iamdave
iamdave

Reputation: 12243

An alternative and potentially more flexible solution is to unpivot the data using cross apply:

declare @t table(id int,diag1 int,diag2 int,diag3 int,diag4 int,diag5 int,diag6 int,diag7 int,diag8 int,diag9 int,diag10 int);
insert into @t (id, diag5) values(1,12345);
insert into @t (id, diag3) values(2,123);
insert into @t (id, diag8, diag1) values(3,123,12345);
insert into @t (id, diag9, diag2) values(4,345,678);

select t.id
      ,d.d
from @t as t
    cross apply(values(diag1),(diag2),(diag3),(diag4),(diag5),(diag6),(diag7),(diag8),(diag9),(diag10)) d(d)
where d.d = 12345;

Output:

+----+-------+
| id |   d   |
+----+-------+
|  1 | 12345 |
|  3 | 12345 |
+----+-------+

Upvotes: 2

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

You can write

'12345' IN (diag1, diag2, ..., diag25)

but there is no syntax for diag1-diag25

Upvotes: 4

Related Questions