I have 3 tables from which I am building a view.
- Patients - has list of basic patient details
- Patient_DiseaseFindings_EntryList - is a separate dt when a patient disease is register the PatientID (FK)from Patients table , along with disease related data is entered
- ExternalDiseasesList - is another table with a menu of disease.
Below is the query to build the Patients Overview view
/****** Script for SelectTopNRows command from SSMS ******/
SELECT
PE.[PatientID]
,[Year]
,[Season]
,[Serial]
,[Age]
,[Length]
,PDF.[Name] as [Disease Finding]
,[DoctorName]
,[Comments_Lab]
,[SampleID]
,[LabAssistant]
FROM [dbo].[Pantient] PE
, [dbo].[Patient_DiseaseFindings_EntryList] PDF
, [dbo].ExternalDiseasesList EDL
WHERE PE.PatientID = PDF.PatientID
and PDF.DiseaseFindingID = EDL.DiseaseFindingID
Group By
PE.[PatientID],[Year],[Season],[Serial],[Age],[Length],PDF.[Name],[DoctorName],[Comments_Lab],[SampleID],[LabAssistant]
I get the output but for each disease I get individual line
But I need the output in one line for each patient with each disease as a column from the ExternalDiseasesList. If the patient has that disease then out Yes else No.
So my query must do a pivot view /Transposed. How should I select to make the pivot?
Please guide. NewB
Aucun commentaire:
Enregistrer un commentaire