Selecting data from multiple rows into a single row

Question

I have a table that has columns like following. Batch number is a database design thing like row number

demand_id batch_number debit status customer
 34 1 yes
 34 2 jack 
 35 1 no 
 35 2 kate 

I want to create a query that would return this:

 demand_id debit status customer
 34 yes jake
 35 no kate 

How can I do that?

Answer 1

Join the table to itself.

So:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.debit_status is not null
and yt2.customer is not null;

Or (if batch_number can reliably be used to fetch the 2 rows needed to make a single row:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.batch_number = 1
and yt2.batch_number = 2;

SQL fiddle.

Answer 2

Group by the demand_id and aggregate the debit_status / customer.

SELECT Demand_Id
 , Max(Debit_Status) Debit_Status
 , Max(Customer) Customer
FROM yourtable GROUP BY Demand_Id;

This may not work if your sample is not representative.

SQL fiddle (data from Phil's).

Answer 3

Getting multiple rows from db and iterate them

if (Collection.Count > 0)
 {
 bool firstRecord = true;
 bool secondRecord = false;
 FirstRowEntiry ent = null;
 foreach (SecondRowEntity ope in opc)
 { 
 if (firstRecord == true)
 { 
 //saving first row data in Ent row
 ent = ope;
 firstRecord = false;
 secondRecord = true;
 continue;
 }
 if (secondRecord == true)
 {
 -----------------------------------------------------
 //checking first and second records match exists 
 ------------------------------------------------------
 if (ope.PrimaryKeyId == ent.PrimaryKeyId && ope.Type == ent.Type)
 {
 // Add all items to listview
 lvi.SubItems.Add(ent.PrimaryKeyId.ToString());
 lvi.SubItems.Add("");
 firstRecord = true;
 secondRecord = false;
 listView1.Items.Add(lvi);
 }
 else
 {
 -----------------------------------------------------------
 // If id and type doenotmatch at all then will follows
 ---------------------------------------------------------------
 bool entDone = false;
 bool opeDone = false;
 if (ent.Type == "Type1" && entDone == false)
 {
 // Add items for required columns and other columns make empty
 lvi = new ListViewItem(ope.PrimaryKeyId.ToString());
 lvi.SubItems.Add(ent.PrimaryKeyId.ToString());
 lvi.SubItems.Add(ent.Type.ToString()); 
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add(""); 
 listView1.Items.Add(lvi);
 entDone = true;
 }
 if (ent.Type == "Type2" && opeDone == false)
 {
 // Add items for required columns and other columns make empty
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add(ent.PrimaryKeyId.ToString());
 lvi.SubItems.Add(ent.Type.ToString()); 
 listView1.Items.Add(lvi);
 entDone = true;
 }
 if (ope.Type == "Type1" && opeDone == false)
 {
 lvi = new ListViewItem(ope.PrimaryKeyId.ToString());
 lvi.SubItems.Add(ope.Type.ToString());
 lvi.SubItems.Add(ope.Type.ToString()); 
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add(""); 
 listView1.Items.Add(lvi);
 opeDone = true;
 }
 if (ope.Type == "Type2" && opeDone == false)
 {
 lvi = new ListViewItem(ope.PrimaryKeyId.ToString());
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add("");
 lvi.SubItems.Add(ope.Type.ToString());
 lvi.SubItems.Add(ope.Type.ToString()); 
 listView1.Items.Add(lvi);
 opeDone = true;
 }
 firstRecord = true;
 secondRecord = false;
 }
 }
 }
 }

• Tom V: I'm not sure how this solves the question since it was tagged sql