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

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
AdmVisitDiagno

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!

StackExchange Database Administrators Q#207654, answer score: 2