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


            }
        }