Query Question

• dougsto

  SSC Veteran

  Points: 298

  More actions

  January 28, 2019 at 1:43 pm

  #373646

  I have a table that contains multiple fields:
  Diagnosis1
  Diagnosis2
  Diagnosis3
  Diagnosis4
  Diagnosis5
  Diagnosis6
  Diagnosis7
  Diagnosis8
  Diagnosis9
  each field only contain codes, the descriptions are in another table. What I need to do is link each code to the description. This may be a stupid question but, is there a way to do this without adding the description table 9 times?
  TIA

• Jonathan AC Roberts

  SSCoach

  Points: 18355

  More actions

  January 28, 2019 at 1:50 pm

  #2020330

  dougsto - Monday, January 28, 2019 1:43 PM

  I have a table that contains multiple fields:
  Diagnosis1
  Diagnosis2
  Diagnosis3
  Diagnosis4
  Diagnosis5
  Diagnosis6
  Diagnosis7
  Diagnosis8
  Diagnosis9
  each field only contain codes, the descriptions are in another table. What I need to do is link each code to the description. This may be a stupid question but, is there a way to do this without adding the description table 9 times?
  TIA

  It would help if you could include the DDL for both tables.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 28, 2019 at 1:57 pm

  #2020331

  dougsto - Monday, January 28, 2019 1:43 PM

  I have a table that contains multiple fields:
  Diagnosis1
  Diagnosis2
  Diagnosis3
  Diagnosis4
  Diagnosis5
  Diagnosis6
  Diagnosis7
  Diagnosis8
  Diagnosis9
  each field only contain codes, the descriptions are in another table. What I need to do is link each code to the description. This may be a stupid question but, is there a way to do this without adding the description table 9 times?
  TIA

  I have been down this road with data similar to yours. Normalize your table and your data problems will go away. Mine did. I was working in MS Access, so I wrote some code to do it for me. (because I had dozens of databases that were improperly designed).

• dougsto

  SSC Veteran

  Points: 298

  More actions

  January 28, 2019 at 1:58 pm

  #2020332

  SELECT   dbo.mdhomadm.Patient_Number, dbo.mdhomadm.Diagnosis9_1, dbo.mdhomadm.Diagnosis9_2, dbo.mdhomadm.Diagnosis9_3, dbo.mdhomadm.Diagnosis9_4, dbo.mdhomadm.Diagnosis9_5,
           dbo.mdhomadm.Diagnosis9_6, dbo.mdhomadm.Diagnosis9_7, dbo.mdhomadm.Diagnosis9_8, dbo.mdhomadm.Diagnosis9_9, dbo.mdicdcde.ICD_Code_Description
  FROM    dbo.mdhomadm LEFT OUTER JOIN
           dbo.mdicdcde ON dbo.mdhomadm.Diagnosis9_1 = dbo.mdicdcde.ICD_Code

  Each patient may have 0-9 diagnosis.I need to select all patients including the ICD_Code_Description for each diagnosis code. Does that make sense?

• dougsto

  SSC Veteran

  Points: 298

  More actions

  January 28, 2019 at 2:04 pm

  #2020333

  Unfortunately, I cannot make changes to the database. We did not develop it.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 28, 2019 at 2:06 pm

  #2020334

  Yes, it does.
  SELECT  dbo.mdhomadm.Patient_Number, dbo.mdhomadm.Diagnosis9_1, dbo.mdhomadm.Diagnosis9_2, dbo.mdhomadm.Diagnosis9_3, dbo.mdhomadm.Diagnosis9_4, dbo.mdhomadm.Diagnosis9_5,
     dbo.mdhomadm.Diagnosis9_6, dbo.mdhomadm.Diagnosis9_7, dbo.mdhomadm.Diagnosis9_8, dbo.mdhomadm.Diagnosis9_9, dbo.mdicdcde.ICD_Code_Description
  FROM  dbo.mdhomadm LEFT OUTER JOIN
     dbo.mdicdcde ON dbo.mdhomadm.Diagnosis9_1 = dbo.mdicdcde.ICD_Code

  The problem is that the way you need this structured is Patient--(1,M)--PatientDiagnoses--(M,1)---ICD_Codes where "ICD_Codes" has the code, and all the other columns that go with it. Then you can do this really easily. If this is an inherited mess, are you allowed to change the structure? (And what if a patient has more than 9 codes?)

• dougsto

  SSC Veteran

  Points: 298

  More actions

  January 28, 2019 at 2:13 pm

  #2020338

  Each patient can only have 9 diagnosis. The table is structured like I this.
  Admission.Diagnosis9_1 to Admission.Diagnosis9_9
  I cannot make changes to any structures, just hoping someone may have an idea.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 28, 2019 at 2:14 pm

  #2020339

  dougsto - Monday, January 28, 2019 2:04 PM

  Unfortunately, I cannot make changes to the database. We did not develop it.

  You might have to create a monster union query. (that's what I had to do)

  SELECT  dbo.mdhomadm.Patient_Number, dbo.mdhomadm.Diagnosis9_1 AS Diagnosis
  FROM  dbo.mdhomadm
  UNION ALL
  SELECT  dbo.mdhomadm.Patient_Number, dbo.mdhomadm.Diagnosis9_2 AS Diagnosis
  FROM  dbo.mdhomadm
  UNION ALL...

  you would have to do one for each of the Diagnosis columns.  Alternatively, you could insert the non-null values into a table in TempDB and query that.

• dougsto

  SSC Veteran

  Points: 298

  More actions

  January 28, 2019 at 2:20 pm

  #2020340

  That was my fear. Thanks for your help.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 28, 2019 at 2:26 pm

  #2020341

  One way of doing it is like this:
  (this is a sample bad table)
  CREATE TABLE DiagnosesBad (
    PatientID INT NOT NULL,
    Cycle TINYINT NOT NULL,
    Diagnosis9_1 VARCHAR(20),
    Diagnosis9_2 VARCHAR(20),
    Diagnosis9_3 VARCHAR(20),
    Diagnosis9_4 VARCHAR(20)
);
GO
INSERT INTO DiagnosesBad VALUES
(1, 1, 'A','B','D','X'),
(2,1,'C','D','X',NULL);

  You could create a table in TempDB  that's normalized and just append all your data there, …. or you could use CROSS APPLY... like this:

  SELECT PatientID, x.cycle, x.Diagnosis
FROM DiagnosesBad
CROSS APPLY (VALUES (Cycle,[Diagnosis9_1])
                    ,(Cycle,[Diagnosis9_2])
                    ,(Cycle,[Diagnosis9_3])
                    ,(Cycle,[Diagnosis9_4])) x (cycle,Diagnosis)
WHERE x.Diagnosis IS NOT NULL;

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 28, 2019 at 2:33 pm

  #2020342

  You might need to unpivot and pivot again your data if you want to call the descriptions table just once and keep the structure in your results. Here's an example:
  
CREATE TABLE SampleTable(
  Id int,
  Diagnosis1 int,
  Diagnosis2 int,
  Diagnosis3 int,
  Diagnosis4 int,
  Diagnosis5 int,
  Diagnosis6 int,
  Diagnosis7 int
);
INSERT INTO SampleTable
VALUES( 1,1,2,3,4,5,6,7), (2,7,6,5,4,3,2,1), (3,5,7,2,5,NULL,NULL,NULL);

  CREATE TABLE DiagnosisCodes(
  DiagnosisId int,
  Description varchar(10)
);
INSERT INTO DiagnosisCodes
VALUES( 1, 'First'),
  (2, 'Second'),
  (3, 'Third'),
  (4, 'Fourth'),
  (5, 'Fifth'),
  (6, 'Sixth'),
  (7, 'Seventh');

  SELECT s.Id,
  MAX( CASE WHEN DiagNo = 1 THEN dc.Description END) AS Diagnosis1,
  MAX( CASE WHEN DiagNo = 2 THEN dc.Description END) AS Diagnosis2,
  MAX( CASE WHEN DiagNo = 3 THEN dc.Description END) AS Diagnosis3,
  MAX( CASE WHEN DiagNo = 4 THEN dc.Description END) AS Diagnosis4,
  MAX( CASE WHEN DiagNo = 5 THEN dc.Description END) AS Diagnosis5,
  MAX( CASE WHEN DiagNo = 6 THEN dc.Description END) AS Diagnosis6,
  MAX( CASE WHEN DiagNo = 7 THEN dc.Description END) AS Diagnosis7
FROM SampleTable s
CROSS APPLY( VALUES( 1, Diagnosis1),
       ( 2, Diagnosis2),
       ( 3, Diagnosis3),
       ( 4, Diagnosis4),
       ( 5, Diagnosis5),
       ( 6, Diagnosis6),
       ( 7, Diagnosis7)) diag(DiagNo, DiagId)
JOIN DiagnosisCodes dc ON diag.DiagId = dc.DiagnosisId
GROUP BY s.Id;

  GO
--DROP TABLE SampleTable, DiagnosisCodes;


  These methods are further explained in:
   http://www.sqlservercentral.com/articles/T-SQL/63681/ 
   http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 28, 2019 at 3:59 pm

  #2020356

  Oh, right... funny, I totally forgot about Kenneth Fisher's example of using CROSS APPLY to unpivot. (See me still thinking in terms of Access??!! BLECH!!)

• okfine08

  SSC Rookie

  Points: 49

  More actions

  February 3, 2019 at 3:35 am

  #2020943

  hi 

   A DIFFERENT WAY OF DOING THIS .....

  HOPE IT HELPS
  🙂
  🙂

  drop TABLE SampleTable
go

  CREATE TABLE SampleTable(
Id int,
Diagnosis1 int,
Diagnosis2 int,
Diagnosis3 int,
Diagnosis4 int,
Diagnosis5 int,
Diagnosis6 int,
Diagnosis7 int
);
INSERT INTO SampleTable
VALUES( 1,1,2,3,4,5,6,7), (2,7,6,5,4,3,2,1), (3,5,7,2,5,NULL,NULL,NULL);

  drop TABLE DiagnosisCodes
go

  CREATE TABLE DiagnosisCodes(
DiagnosisId int,
Description varchar(10)
);
INSERT INTO DiagnosisCodes
VALUES( 1, 'First'),
(2, 'Second'),
(3, 'Third'),
(4, 'Fourth'),
(5, 'Fifth'),
(6, 'Sixth'),
(7, 'Seventh');

  select * from DiagnosisCodes
go

  select * from SampleTable
go

  SELECT a.id, 
       b.description AS Diagnosis1, 
       c.description AS Diagnosis2, 
       d.description AS Diagnosis3, 
       e.description AS Diagnosis4, 
       f.description AS Diagnosis5, 
       g.description AS Diagnosis6, 
       h.description AS Diagnosis7 
FROM   sampletable a 
       LEFT JOIN diagnosiscodes b 
              ON a.diagnosis1 = b.diagnosisid 
       LEFT JOIN diagnosiscodes c 
              ON a.diagnosis2 = c.diagnosisid 
       LEFT JOIN diagnosiscodes d 
              ON a.diagnosis3 = d.diagnosisid 
       LEFT JOIN diagnosiscodes e 
              ON a.diagnosis4 = e.diagnosisid 
       LEFT JOIN diagnosiscodes f 
              ON a.diagnosis5 = f.diagnosisid 
       LEFT JOIN diagnosiscodes g 
              ON a.diagnosis6 = g.diagnosisid 
       LEFT JOIN diagnosiscodes h 
              ON a.diagnosis7 = h.diagnosisid 
ORDER  BY a.id 

  
  

• grother

  Grasshopper

  Points: 11

  More actions

  February 26, 2019 at 2:31 am

  #2022942

  So you have a patient table with 9 diagnosis fields.
  Just fill the 9 diagnosis fields with a union into a two field table (PatientID, DiagnosisID)
  Make shure you select only the entries with a Diagnosis.
  This you can easily Join with your diagnosisText Table
  
  SELECT PatID, diagID, diagText
FROM
(
SELECT PatID, DiagID_01 AS DiagID FROM tblDiagnosis WHERE diagID_01 is not null
UNION
SELECT PatID, DiagID_02 FROM tblDiagnosis WHERE diagID_02 is not null
...
UNION
SELECT PatID, DiagID_09 FROM tblDiagnosis WHERE diagID_09 is not null
)un
INNER JOIN tblDiagnososText t  ON t.DiagID=un.diagID[/code]