You are here:

MS SQL Server/changing rows into columns

Advertisement


Question
Hello, I have the following query which works:

SELECT patient_study.patient_studyid, location.loc_desc, followup_diagnosis_psoriasis.followDate, clinical_assessment.visit_type, clinical_assessment.pasi
from patient_study
inner join patient on patient_study.patientid = patient.patientid
inner join location on patient.location = location.code
left join followup_diagnosis_psoriasis on patient_study.patientid = followup_diagnosis_psoriasis.patientid
left join clinical_assessment on followup_diagnosis_psoriasis.patientid = clinical_assessment.patientid and followup_diagnosis_psoriasis.followupNo = clinical_assessment.visit_type

So the query above gives me a list of all the patients, there location and then whether or not they have a followup date and whether or not they have had a visit (numbered from 1 - 12, using the original column heading clinical_assessment.visit_type) and what was there pasi score (only 1 pasi score and followup date per visit)

what I would like to do is turn the visit numbers 1 - 12 as column headings so each patient study id will have 2 rows per column, one for the date and the other for the pasi score at that number visit. If they don't have a pasi score and/or date the value will be null. Likewise if they haven't had a visit number 5 then everything under column visit_5 will be null.

I know that a pivot can be used, but I have no idea how to go about this especially with a query that incorporates this many tables.

any help would be much appreciated

regards

tejus

Answer
Apologies for the delay. I think I have an idea, but pivot queries are (to my way of thinking) such unintuitive things that I always have to "play around" with them.

Can you do me a favor and respond with
1) some CREATE statements to create the tables referenced in the query (or at least the columns so-referenced... if the tables have tons of columns we don't need to concern ourselves with those not used in the query)

2) Some INSERT statements to seed the tables with some sample data (or, if easier, some CSV files that can be bulk loaded into the tables)

Also: What version of SQL Server is this?

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.