Link to home
Start Free TrialLog in
Avatar of ttta83
ttta83

asked on

DB2 - Display multiple records in 1 row

I have 2 tables for message (email) center

MESGE table
	
MESGE_ID  	INTEGER (IDENTITY)		
SENDR_EMPL_ID	INTEGER		
MESGE_SUBJ	VARCHAR		
MESGE_TEXT  	VARCHAR		
CNTRTR_ID	INTEGER		
ORGNL_MESGE_ID	SMALLINT	
MAINT_TMSTP	TIMESTMP	
--------------------------------------------------------------
MESGE_RCPNT table

MESGE_ID	SMALLINT
EMPL_ID		INTEGER
RCPNT_TYPE_CODE	CHAR
MAINT_USER_ID	CHAR
MAINT_TMSTP	TIMESTMP

Open in new window


RCPNT_TYPE_CODE:
T: TO
C: CC

The data look like this

MESGE table


MESGE_ID	SENDR_EMPL_ID	MESGE_SUBJ	MESGE_TEXT	CNTRTR_ID	ORGNL_MESGE_ID	MAINT_TMSTP	
1	551010	TEST SUBJECT	TEST BODY	10016		10/24/2012 9:57:38 AM	
3	2226	TEST SUBJECT 2	TEST BODY 2	10016		10/24/2012 1:52:16 PM	
4	551010	RE: TEST SUBJECT 2	REPLY TEST BODY 2	10016	3	10/24/2012 2:13:51 PM	
5	2226	RE: TEST SUBJECT 2	SECOND REPLY TEST BODY 2	10016	4	10/24/2012 2:21:16 PM	

MESGE_RCPNT table

MESGE_ID	EMPL_ID	RCPNT_TYPE_CODE	MAINT_TMSTP	
1	541415	C	10/24/2012 11:01:14 AM	
1	551918	T	10/24/2012 11:10:22 AM	
1	541221	T	10/24/2012 1:34:11 PM	
3	551010	T	10/24/2012 1:52:40 PM	
4	2226	T	10/24/2012 2:14:24 PM	
5	551010	T	10/24/2012 2:21:28 PM	

Open in new window


I have the SQL that gives me the data that I need

SELECT A.CNTRTR_ID, 
A.MESGE_ID,
A.SENDR_EMPL_ID AS MSG_FROM,
STRIP(C.LAST_NAME) CONCAT ', ' CONCAT STRIP(C.FIRST_NAME) CONCAT ' ' CONCAT STRIP(C.MIDLE_INIT) AS MESGE_FROM,
B.RCPNT_TYPE_CODE , B.EMPL_ID, STRIP(D.LAST_NAME) CONCAT ', ' CONCAT STRIP(D.FIRST_NAME) CONCAT ' ' CONCAT 
STRIP(D.MIDLE_INIT) AS MESGE_TO,
A.MESGE_SUBJ,
A.MESGE_TEXT,
A.MAINT_TMSTP AS MESGE_DATE, 
ORGNL_MESGE_ID
FROM MESGE A
LEFT JOIN MESGE_RCPNT B
ON B.MESGE_ID = A.MESGE_ID
LEFT JOIN EMPL C
ON C.EMPL_ID = A.SENDR_EMPL_ID
LEFT JOIN EMPL D
ON D.EMPL_ID = B.EMPL_ID
WHERE A.CNTRTR_ID = 10016
ORDER BY A.MESGE_ID, B.RCPNT_TYPE_CODE DESC

Open in new window


The data look like this

CNTRTR_ID	MESGE_ID	MSG_FROM	MESGE_FROM	RCPNT_TYPE_CODE	EMPL_ID	MESGE_TO	MESGE_SUBJ	MESGE_TEXT	MESGE_DATE	ORGNL_MESGE_ID	
10016	1	551010	SMITH, JOE Z	T	551918	GARZA, DAN 	TEST SUBJECT	TEST BODY	10/24/2012 9:57:38 AM		
10016	1	551010	SMITH, JOE Z	T	541221	CLINTON, NICK 	TEST SUBJECT	TEST BODY	10/24/2012 9:57:38 AM		
10016	1	551010	SMITH, JOE Z	C	541415	SUEREZ, MARIA 	TEST SUBJECT	TEST BODY	10/24/2012 9:57:38 AM		
10016	3	2226	CARTER, MIKE Y	T	551010	SMITH, JOE Z	TEST SUBJECT 2	TEST BODY 2	10/24/2012 1:52:16 PM		
10016	4	551010	SMITH, JOE Z	T	2226	CARTER, MIKE Y	RE: TEST SUBJECT 2	REPLY TEST BODY 2	10/24/2012 2:13:51 PM	3	
10016	5	2226	CARTER, MIKE Y	T	551010	SMITH, JOE Z	RE: TEST SUBJECT 2	SECOND REPLY TEST BODY 2	10/24/2012 2:21:16 PM	4	

Open in new window


Would you please help me to combine the first 3 records (MESGE_ID = 1) in 1 line, so it can look like this

CNTRTR_ID	MESGE_ID	MSG_FROM	MESGE_FROM	RCPNT_TYPE_CODE	EMPL_ID	MESGE_TO	CC_RCPNT_TYPE_CODE	CC_EMPL_ID	CC_NAME	MESGE_SUBJ	MESGE_TEXT	MESGE_DATE	ORGNL_MESGE_ID	
10016	1	551010	SMITH, JOE Z	T	551918;541221	GARZA, DAN; CLINTON, NICK 	C	541415	SUEREZ, MARIA	TEST SUBJECT	TEST BODY	10/24/2012 9:57:38 AM		

Open in new window


Thank you very much.
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

getting multiple record to appear in a single row can be done in 2 ways:
if you want a single long string it can be achieved using recursive sql
if you want to have the different columns, you have to use joins

if you always have a max of 3 rows, you just take the query you already have, put it in a cte and use a self join 2 times
if the number of rows can be more than 3, you have to decide what the maximum can be to implement joins
Avatar of ttta83
ttta83

ASKER

Hi momi_sabag,

We don't know the number of records.  It's not alsways 3, we can have 6 (4 in TO line and 2 in CC line).

Thanks,
Avatar of Theo Kouwenhoven
Hi ttta83

What if it will be 7 or 10 or more?
and is it critical for you to have it separated to different columns or can you live with one long string?
who will receive the results of this query?
Avatar of ttta83

ASKER

It can be 7 or more.  I want to group together all the T(id and name) and C(id and name).

The results will be displayed on the screen, similar to outlook.  

Thanks for the help.
so you will have to do it in code
the only way to do that in a query will generate a single string column with all the data that you will need to parse in your application anyway
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ttta83

ASKER

Thanks a lot!!!