One to many relationship view data in a single row SQL query

Question

Here are three tables:

Table1: MedicineForPrescription

Id	MedicineID	prescriptionId
12	14	14481
13	15	14481
14	16	14481

Table2: Prescription

Id	PatientId
14481	1

Table3: Patient

Id	FirstName	LastName
1	Sam	Suan

I want to show Like

FirstName	LastName	PrescriptionId	Medicine1	Medicine2	Medicine3
sam	suan	14481	14	15	16

Can anyone please provide me the sql server query?

Answer 1

For a fixed maximum number of medecines per patient prescription, you can join the tables, then pivot with conditional aggregation:

select pa.firstName, pa.lastName, me.prescriptionId,
    max(case when me.rn = 1 then medecineId end) as medecine1,
    max(case when me.rn = 2 then medecineId end) as medecine2,
    max(case when me.rn = 3 then medecineId end) as medecine3
from patient pa
inner join prescription pr on pr.patientId = pa.id
inner join (
    select me.*, row_number() over(partition by prescriptionId order by id) rn
    from medecineForPrescription me
) me on me.prescriptionId = pr.id
group by pa.id, pa.firstName, pa.lastName, me.prescriptionId