June 25, 2015 at 3:10 pm
Hello Folks,
I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example:
Multiple records with the same record ID:
RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302
1380900 XZ01 12 1/6/2015 L405
1380900 XZ01 12 1/6/2015 P302 1004
INTO a single record:
RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302 L405 1004
Please see attachment for a better picture of the table above.
Thank you very much!
R.
June 25, 2015 at 3:55 pm
Given the data, this works:
SELECT RecID
, BO_ID
, Code
, Code_Date
, MAX(CD1) AS Msg1
, MAX(CD2) AS Msg2
, MAX(CD3) AS Msg3
FROM
(SELECT 1380900 AS RecID
,'XZ01' AS BO_ID
, 12 AS Code
, '1/16/2015' AS Code_Date
, 'P302' AS CD1
, NULL AS CD2
, NULL AS CD3
UNION ALL
SELECT 1380900 AS RecID
,'XZ01' AS BO_ID
, 12 AS Code
, '1/16/2015' AS Code_Date
, NULL AS CD1
, 'L405' AS CD2
, NULL AS CD3
UNION ALL
SELECT 1380900 AS RecID
,'XZ01' AS BO_ID
, 12 AS Code
, '1/16/2015' AS Code_Date
, NULL AS CD1
, NULL AS CD2
, 1004 AS CD3) X
GROUP BY RecID
, BO_ID
, Code
, Code_Date;
That said, you'll get much better and tested answers if you learn to post your questions according to best practices[/url]. Jeff Moden wrote a great article on it, and it's well worth your time to read it.
June 25, 2015 at 4:33 pm
Thank you very much! that worked great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply