6

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?

8

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.

8

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).

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


            }
        }
  • 1
    I'm not sure how this solves the question since it was tagged sql – Tom V - Team Monica Dec 18 '15 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.