Merging multiple rows into one row with several columns
This means the following;
1. Several rows from output have to be combined into one row with several added columns in input.
2. Criteria for 'merging' rows are based on the values of several columns in input, if these all match the row can be merged.
3. When there are over 4 identical rows in input for one merge, the data from any of these rows is fine. Result in output is limited to 3 extra columns.
4. A final row in output needs to display the true amount of rows in input.
I've added a simplified and shortened example of the two files in question as an attachment to this posting.
My first instinct, based solely on experience with excel macro's; 1. Concatenate the rows that need to be identical for the merger. Save in temporary row.
2. Create a new table. For each row check if the value in the temporary row has been used before, then write the value in the proper column and update the counter.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
I want the duplicate rows to be condensed into one row per employee.
When all of the rows are appended, I end up with employees with multiple rows -- but one row has a value for ID1, one row has a value for ID2 and another row has a value for ID3, etc. There isn't a common business rule to indicate when a certain employee would have multiple rows so I can't make the SSN a primary key and have the append reject the duplicate rows.
I have two columns say column A and D which consists of integer values and the values of the columns are say in the range 1 - 100 ( both the columns ). so there is a row say in column A value is 38 and in column D corresponding value is 37.
So now in the output there should not be any other record with having value 38 and 37 either in column A or D .ie. all those rows which contains these values other than this rows should be omitted.
Similarly for other rows and values also .I tried with the following query but didn't worked
should be converted to E1 E2 E3 as Column headers.
Can anyone help me with some solution for this Conversion of Row data to COlumns.
For example I have 7 rows showing on the sub-report which is what I need, but I want to add a button next to each row of data and an onClick event to allow me to hide the row from view andallow me to still see the other 6 rows? I don't want to delete this row, just hide it, there will be times where I need to hide multiple rows at the same time.
How do I insert the desired rows without moving EVERYTHING on the formdown?
1. If there exists a discount amount in a row with a null or blank value in the adjacent discount/promo code column, insert a text value "Other Discount" to all rows within that same order (orderid).
In other words, an order was placed with an unidentified discount code (text) provided.
1. Distribute a discount code (text) used in a purchase to rows of the same order (with the same orderid). Currently, an order with 3 purchased items only includes the discount code on one of the rows.
I need this text value replicated in each of the 3 line items or rows. (this is similar to my first task above)
2. Distribute the discount amount (number), which also only included in only 1 row for a multi-item rode, evenly across all ordered items so that when I aggregate all orders by OrderId, I will have a "net order revenue" number.
For example, if a person orders 3 different items at $25 each, receives a $15 discount, I need each row to be reduced to $20.
Both tables have a record that a different Serial by row.
The important columns are STOREID (store ID), CUSTID (Customer ID), and QTY (Order amount). I want to select 25 rows per store that reflect the top 25 customers in descending order by their order amount values.
SELECT TOP 25. only gives me the top 25 rows regardless of store so I'm missing something.
I allready know how to transfer it by looping through the array, adding 1 row at a time. However, this is not that effective, and I believe that one of the problems stems from the fact that the main table has its indexes updated each time a row is added.
I could probably work around this by first moving the array into a temporary table, and then use SQL to add all the rows at once.