CODEWITHSUNDEEP
sqlsql-servert-sql
Campbell Reid
Campbell Reid

Reputation: 139

Convert multiple rows for one column into multiple columns (on the fly)

I've had a look around for solutions to my problem, but they all seem to involve seemingly complicated methods like pivot tables, cross-tabs, etc. It just seems like there must be a simpler solution to what doesn't seem to me to be a particularly complex problem. I am using MS SQL server 2014 (64-bit), and MS Report Builder version 3. (My OS is NT 6.3). We have a relational database with a large number of tables (too many to list here), but my code below gives the pertinent ones.

At the moment, I am concatenating patient diagnoses from multiple rows into a single row, using the STUFF...FOR XML PATH method. What I would like to do is have one column per diagnosis found (the boss wants to do some post-processing in Excel, and he would like it in this format). The number of diagnoses is an indeterminate number between 0 and 5 inclusive.

The output I have is this (this is very simplified - there are actually 10 columns, but it illustrates the point):

Patient  Diagnoses
ID1      C32.1,HN02
ID2      C13.9
ID3      C11.9,HN04
ID4      C08.9,HN08
ID5      C80,XX01,BR22
ID6

Without any concatenation, the output is this:

Patient  Diagnoses
ID1      C32.1
ID1      HN02
ID2      C13.9
ID3      C11.9
ID3      HN04
ID4      C08.9
ID4      HN08
ID5      C80
ID5      XX01
ID5      BR22
ID6

The output I would like is this:

Patient  Diagnosis1  Diagnosis2  Diagnosis3 Diagnosis4
ID1      C32.1       HN02
ID2      C13.9
ID3      C11.9       HN04
ID4      C08.9       HN08
ID5      C80         XX01        BR22
ID6

The code I am using is (essentially) this:

SET transaction isolation level read uncommitted;

SELECT top (1000)
p.PatientId
,'Diagnoses'= STUFF((
        SELECT DISTINCT
        ',' + LTRIM(RTRIM(diag.DiagnosisCode))

        FROM
            Diagnosis diag
            INNER JOIN CourseDiagnosis cd on (cd.DiagnosisSer=diag.DiagnosisSer and cd.CourseSer=c.CourseSer)

        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

FROM
Patient p
INNER JOIN Course c on p.PatientSer=c.CourseSer

Does anyone have any bright ideas?

Upvotes: 3

Views: 764

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would do this using conditional aggregation, but the key is row_number():

SELECT patientId,
       MAX(CASE WHEN seqnum = 1 THEN DiagnosisCode END) as DiagnosisCode_1,
       MAX(CASE WHEN seqnum = 2 THEN DiagnosisCode END) as DiagnosisCode_2,
       MAX(CASE WHEN seqnum = 3 THEN DiagnosisCode END) as DiagnosisCode_3,
       MAX(CASE WHEN seqnum = 4 THEN DiagnosisCode END) as DiagnosisCode_4
FROM (SELECT p.patientId, d.DiagnosisCode,
             ROW_NUMBER() OVER (PARTITION BY p.patientId ORDER BY d.DiagnosisCode) as seqnum
      FROM Patient p JOIN
           Course c 
           ON p.PatientSer = c.CourseSer JOIN
           CourseDiagnosis cd 
           ON cd.CourseSer = c.CourseSer JOIN
           Diagnosis d
           ON cd.DiagnosisSer = d.DiagnosisSer
     ) d
GROUP BY patientId;

Upvotes: 2

Related Questions