How to combine multiple rows into single row with different column per row's data when ID is the same

Question

I have this result from multiple inner joins. I would like to have this rows combine in 1 row with additional columns per data from the rows.

Select fp.idfp, fp.idperson, p.fname+' '+p.mname+' '+p.lname as Patient,
fp.date, m.idmed, n.medname+' '+n.meddosage+' '+n.medtype as MedicineName, m.quantity, m.date as [Date Dispense]
from FPTRANSAC fp
Inner Join PRECORD p
On fp.idperson = p.idperson

inner join MEDOUT m
ON fp.idfp = m.idtransac

inner join NEWMED n
on m.idmed = n.idmed

union all

Select i.idimmu, i.idperson, p.fname+' '+p.mname+' '+p.lname as Patient,
i.date, m.idmed, n.medname+' '+n.meddosage+' '+n.medtype as MedicineName, m.quantity, m.date as [Date Dispense]
from IMMUTRANSACTION i
Inner Join PRECORD p
On p.idperson = i.idperson

inner join MEDOUT m
ON i.idimmu = m.idtransac

inner join NEWMED n
on m.idmed = n.idmed

This is the result:

idfptran | idperson | Patient     | date     | idmed | MedicineName  | quantity | Date Dispense
F-1      | 00001    | Jenny Jones | datehere | 1     | Cetirizine    | 5        | datehere
F-1      | 00001    | Jenny Jones | datehere | 3     | Tylenol       | 8        | datehere
I-1      | 00015    | Mark Sawyer | datehere | 2     | Salbutamol    | 2        | datehere  
I-1      | 00015    | Mark Sawyer | datehere | 4     | Amoxicillin   | 3        | datehere
I-1      | 00015    | Mark Sawyer | datehere | 7     | Carbocisteine | 3        | datehere  

I would want to have this table like this but I don't know if it is possible. Can you please help? I've been trying to solve this for days now :( Please help. I am using mssql. For the Date Dipense, it can get the last row's value of the same ID or the first.

idfptran | idperson | Patient     | date     | idmed | MedicineName  | quantity | idmed | MedicineName | quantity | idmed | MedicineName | quantity | Date Dispense
F-1      | 00001    | Jenny Jones | datehere | 1     | Cetirizine    | 5        | 3     | Tylenol      | 8        |       |              |          | datehere
I-1      | 00015    | Mark Sawyer | datehere | 2     | Salbutamol    | 2        | 4     | Amoxicillin  | 3        | 7     | Carbocisteine| 3        | datehere

Answer 1

Instead of building a union I would propose first collecting all person related data into a table or query and then adding the other data like idmed, medicine name, ... from the other source tables in the next layer(s) using outer join(s) for each source.

Answer 2

Here is sample code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ConsoleApplication108
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("idfptran", typeof(string));
            dt.Columns.Add("idperson", typeof(string));
            dt.Columns.Add("Patient", typeof(string));
            dt.Columns.Add("date", typeof(DateTime));
            dt.Columns.Add("idmed", typeof(int));
            dt.Columns.Add("MedicineName", typeof(string));
            dt.Columns.Add("quantity", typeof(int));
            dt.Columns.Add("Date Dispense", typeof(DateTime));
            dt.Rows.Add(new object[] {"F-1", "00001", "Jenny Jones", DateTime.Parse("4/8/19"), 1, "Cetirizine", 5, DateTime.Parse("4/8/19")});
            dt.Rows.Add(new object[] {"F-1", "00001", "Jenny Jones", DateTime.Parse("4/8/19"), 3, "Tylenol", 8, DateTime.Parse("4/8/19")});
            dt.Rows.Add(new object[] {"I-1", "00015", " Mark Sawyer", DateTime.Parse("4/8/19"), 2, "Salbutamol", 2, DateTime.Parse("4/8/19")});
            dt.Rows.Add(new object[] {"I-1", "00015", " Mark Sawyer", DateTime.Parse("4/8/19"), 4, "Amoxicillin", 3, DateTime.Parse("4/8/19")});
            dt.Rows.Add(new object[] {"I-1", "00015", " Mark Sawyer", DateTime.Parse("4/8/19"), 7, "Carbocisteine", 3, DateTime.Parse("4/8/19")});

            var groups = dt.AsEnumerable().GroupBy(x => new { idperson = x.Field<string>("idperson"), dispense = x.Field<DateTime>("Date Dispense") }).ToList();

            int maxMedicenes = groups.Select(x => x.Count()).Max(x => x);

            DataTable pivot = new DataTable();
            pivot.Columns.Add("idfptran", typeof(string));
            pivot.Columns.Add("idperson", typeof(string));
            pivot.Columns.Add("Patient", typeof(string));
            pivot.Columns.Add("date", typeof(DateTime));

            for (int i = 1; i <= maxMedicenes; i++)
            {
                pivot.Columns.Add("idmed_" + i.ToString(), typeof(int));
                pivot.Columns.Add("MedicineName_" + i.ToString(), typeof(string));
                pivot.Columns.Add("quantity_" + i.ToString(), typeof(int));
            }
            pivot.Columns.Add("Date Dispense", typeof(DateTime));

            foreach (var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["idfptran"] = group.First().Field<string>("idfptran");
                newRow["idperson"] = group.Key.idperson;
                newRow["Patient"] = group.First().Field<string>("Patient");
                newRow["date"] = group.First().Field<DateTime>("date");

                int i = 1;
                foreach (DataRow row in group)
                {
                    newRow["idmed_" + i.ToString()] = row.Field<int>("idmed");
                    newRow["MedicineName_" + i.ToString()] = row.Field<string>("MedicineName");
                    newRow["quantity_" + i.ToString()] = row.Field<int>("quantity");
                    i++;
                }
                newRow["Date Dispense"] = group.Key.dispense;
            }

        }
    }
}