MS SQL Server/changing rows into columns
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
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
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?