Combine two rows into one row
Is there any way to combine multiple rows into one row? I have a field called MILESTONES that has 3 different milestones and I did a decode statement to separate the RECEIVED_DATES which are now DOCS_COMPLETE_DATE, UPLOAD_DATE, and FORWARD_TO_ONEPOINT_DATE.
There will be NULLS per some of the milestones dates. Somehow I would like to try combining these multiple trx rows into one row, if possible.
Can you help?
Leave a comment...
- 4 Comments
- I am not sure about your output (it is pivot or not?).. could you post an example ?#1; Tue, 11 Dec 2007 15:22:00 GMT
- Attached is an example of the output. I don't need to see the MILESTONE name in the output, just one trx# and the dates displayed on one row.#2; Tue, 11 Dec 2007 15:23:00 GMT
- but what if you have two (or more?) dates on UPLOAD_DATE / FORWARD_TO_ONEPOINT_DATE for a given transaction, which date will be the one you like ?
sounds like a simple group by with max/min for me..#3; Tue, 11 Dec 2007 15:25:00 GMT
- The first date and time, if there is more than one date. It doesn't seem that simple to me. Here is part of the select statement, I commented out the decode statements to separate the RECEIVE DATE. I've did a min funtion on the RECEIVE DATE.
SELECT DISTINCT MB.PROCESSING_CENTER_CODE AS REGION,
E.ENTITY_NO AS TRANSACTION_NO,
LOOKUP_DESCRIPTION_F ('Financial Trx Milestone', EMV.ENTITY_MILESTONE_CODE) AS MILESTONE,
/*DECODE(LOOKUP_DESCRIPTION_F ('Financial Trx Milestone', EMV.ENTITY_MILESTONE_CODE), 'Documents Completed and Submitted to Banker', EMV.MILESTONE_ACT_DATE, NULL) AS DOCS_COMPLETE_DATE,
DECODE(LOOKUP_DESCRIPTION_F ('Financial Trx Milestone', EMV.ENTITY_MILESTONE_CODE), 'Routed to LSC-Upload Completed', EMV.MILESTONE_ACT_DATE, NULL) AS UPLOAD_DATE,
DECODE(LOOKUP_DESCRIPTION_F ('Financial Trx Milestone', EMV.ENTITY_MILESTONE_CODE), 'Collateral Upload Complete-Forwarded for One Point Updates', EMV.MILESTONE_ACT_DATE, NULL) AS FORWARD_TO_ONEPOINT_DATE, */
MIN(EMV.MILESTONE_ACT_DATE) AS RECEIVED_DATE,
Thanks.#4; Tue, 11 Dec 2007 15:25:00 GMT