How do you concatenate an entire column with a group by?

Question

Today, I saw that our residents have put a bajillion hours into an excel spreadsheet, tracking everything they need to know about our patients.

The excel sheet is really good for humans to read from, but absolutely horrendous for machines. I'd like to help them out, but I'm stuck on one part: creating a list of every diagnosis per patient - and presenting it in a single field.

I've got this, which will concatenate the entire table:

    select stuff( (
    select top 12  ', ' + icd10 + ' ' + icd10.description   from 
    AdmVisitDiagnoses dx 
    left join ehs_icd10 icd10 on replace(dx.Code,'.','') = icd10.icd10
    where icd10 is not null
    for xml path ('')),1,2,'')

which will produce a result like this:

I10 Essential (primary) hypertension, E780 Pure hypercholesterolemia, E780 Pure hypercholesterolemia, I10 Essential (primary) hypertension, I8291 Chronic embolism and thrombosis of unspecified vein, I8291 Chronic embolism and thrombosis of unspecified vein, F205 Residual schizophrenia, F209 Schizophrenia unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified

but I think I'm barking up entirely the wrong tree. Also, the performance is horrendous (not a deal breaker, since it will be automated, but still something to be mindful of).

Question: How would you break this up by a foreign key?

Some sort of pivot? The number of diagnoses is quite variable...

Ideally, it would be something I can easily smush into a larger query, but there's obviously ways to work around that if needed.

Here's my current solution, but I'm pretty sure it's not a good one:

    create function dbo.stuffDx(@VisitID varchar(55))
    returns varchar(max)

    as
    begin
    declare @string varchar(max)

    set @string = (select stuff( (
    select top 500  ', ' + isnull(icd10,'') + ' ' + isnull(icd10.description,'')   from 
    AdmVisitDiagnoses dx 
    left join ehs_icd10 icd10 on replace(dx.Code,'.','') = icd10.icd10
    where icd10 is not null
    and VisitID = @VisitID
    for xml path ('')),1,2,''))

    return @string

    end

Here's the data:

AdmVisitDiagnoses:

    VisitID             Code  
    OL0-20100812131135  250.00  
    OL0-20100812131135  295.90  
    OL0-20100812134136  250.00  
    OL0-20100812134136  401.9  
    OL0-20100812134506  250.00  
    OL0-20100812134506  401.9  
    OL0-20100812135224  295.30  
    OL0-20100812135224  250.00  
    OL0-20100817085834  295.90  
    OL0-20100817085834  272.0  

icd10:

    icd10   description  
    A000    Cholera due to Vibrio cholerae 01 biovar cholerae  
    A001    Cholera due to Vibrio cholerae 01 biovar eltor  
    A009    Cholera unspecified  
    A0100   Typhoid fever unspecified  
    A0101   Typhoid meningitis  
    A0102   Typhoid fever with heart involvement  
    A0103   Typhoid pneumonia  
    A0104   Typhoid arthritis  
    A0105   Typhoid osteomyelitis  
    A0109   Typhoid fever with other complications  

Answer 1

You are "barking up entirely the wrong tree"!

What you want is something like:

CREATE TABLE patient_diagnosis
(
  patient_id INTEGER   NOT NULL, -- FK to patient table
  admission_time TIMESTAMP NOT NULL,
  diagnosis_id INTEGER NOT NULL, -- FK to diagnnosis table. Under **no** circumstances
                                 -- allow this to be free text! You might want to allow
                                 -- the text in here but from a dropdown list!
  initial_diagnosis_time TIMESTAMP NOT NULL,
  condition_cleared_time TIMESTAMP -- nullable because a patient might have an incurable 
                                   -- condition like Cystic Fibrosis or Alzheimer's?
);

PRIMARY KEY something like (patient_id, diagnosis_id, initial_diagnosis_time)
or (patient_id, admission_time, diagnosis_id) depending on your needs.

So, for a patient with multiple diagnoses, you will have multiple records - you'll be able to SELECT current diagnoses and history. This is ideal for spreadsheets and much easier than a long comma-separated list.

SQL is not good at manipulating such lists - it's brilliant at "slicing and dicing" small, simple atomic records. For example, "How many people with a diagnosis of bubonic plague were admitted in the first quarter of last year?".

From here (Codd's rules):

Rule 2: The guaranteed access rule:

Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

Comma separated lists violate this basic rule derived from relational algebra which is the only mathematically sound theory underlying data storage and manipulation!

Answer 2

ICD10 codes need to be ordered appropriately, elderly patients can be admitted for a Primary Condition i.e. Cancer, but have dozens of other co-morbidities i.e. Asthma or Diabetes. SQL will return values in any order it feels like.Using TOP 12 could result in the primary Diagnosis being excluded.

When Clinicians populate their spreadsheets they are applying an ordering system (a cancer patient may be having an in-growing toe nail sorted out - so cancer is not the primary in this case). Sometimes ICD10 & OPCS4 codes have to go in a special order, it's common to see a Primary 'Cxxx' code followed by a Secondary 'Dxxx' code.

Speak to your clinicians first about ordering logic? You need to have that before you go any further. All the best SQL solutions start with a good requirements document :)

Answer 3

Something you will want to do, when you are using XML like this, is to use TYPE and value() to protect against XML character entization - where characters such as < get encoded as <

You will also want to extract the text() node from the XML for a performance boost.

So, instead of this : for xml path (''))

use this : for xml path(''), TYPE).value('(./text())[1]','NVARCHAR(MAX)')

Answer 4

Some good advice already here but none that targets the question you have.

You do a group by on VisitID in the main query and then do the concatenation in a subquery that filters on the VisitID from the main query.

select AVD.VisitID,
       stuff((
             select ', ' + I.icd10 + ' ' + I.description
             from dbo.AdmVisitDiagnoses as AVD2
               inner join dbo.icd10 as I
                 on I.icd10 = AVD2.Code
             where AVD2.VisitID = AVD.VisitID
             for xml path(''), type
             ).value('text()[1]', 'nvarchar(max)'), 1, 2, '')
from dbo.AdmVisitDiagnoses as AVD
group by AVD.VisitID;