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

The table you are trying to create would not be in first normal form. — Honeyboy Wilson
– Honeyboy Wilson, Commented Apr 8, 2019 at 15:48
This style of display sort of goes against everything you are trying to get away from with normalization. Is there some reason you want it displayed this way? — haag1
– haag1, Commented Apr 8, 2019 at 15:51
@haag1 Actually I have 2 transactions here. 1. The ImmuTransaction and FPTransac. 2. MEDOUT So, in MEDOUT, I am just calling the id of the transaction(ImmuTransaction or FPTransac) to link all the medicine asked by the patient. The reason why I want the table to be like that so that every transaction, they can see what medicines were asked by the patient. — meandyou
– meandyou, Commented Apr 8, 2019 at 15:55
What you want is a pivot table Where you are grouping by a person. I will work on code. — jdweng
– jdweng, Commented Apr 8, 2019 at 16:02

xfh2002 · Accepted Answer · 2019-04-08 16:47:19Z

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.

answered Apr 8, 2019 at 16:47

xfh2002

563 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

meandyou Over a year ago

Hi. I didn't get what your trying to tell me. I'm sorry. A newbie still learning and struggling and wondering if this is even possible.

jdweng · Accepted Answer · 2019-04-08 16:33:58Z

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;
            }

        }
    }
}

Thank you. I really appreciate your help. I forgot to tell you that the datas are not just that, every now and then, more transactions are being added. I still have 3 more tables to join also, I just used 2 transaction table this time.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related