IBM Mainframe Forum

by **Analyst_Kumar** » Fri May 29, 2015 10:04 pm

Hello All,

I have a DB2 table and is the sample data.

[b]EmpID EmpName Project Name StartDate[/b]
E001 Emp1 Project1 01-01-2014
E001 Emp1 Project2 04-01-2015
E002 Emp2 Project1 05-07-2014
E003 Emp3 Project6 03-09-2014
E004 Emp4 Project3 01-09-2014
E004 Emp4 Project4 01-01-2015
E004 Emp4 Project5 09-10-2015

Requirement: Here the condition is, The Employee can work on maximum of 3 projects at any point of time.

I want to select the data from the above the table in in the following format.

Select all

[b]EmpID EmpName Project Details[/b]
E001|Emp1|Project1|01-01-2014|Project2|04-01-2015
E002|Emp2|Project1|05-07-2014
E003|Emp3|Project6|03-09-2014
Emp4|Emp4|Project3|01-09-2014|Project4|01-01-2015|Project5|09-10-2015

Can anyone suggest an SQL for this requirement ?

by **alexm** » Tue Jun 16, 2015 10:45 pm

Hi Analyst_Kumar,

you may borrow some XML functionality doing the trick for you (however, there would be alternative solutions).

Here's a sample table and data:

Select all

CREATE TABLE TEST
( C1 CHAR(2) NOT NULL WITH DEFAULT
, C2 CHAR(2) NOT NULL WITH DEFAULT
, C3 CHAR(4) NOT NULL WITH DEFAULT
) IN database.tablespace;
INSERT INTO TEST (C1,C2,C3) VALUES('E1','P1','2000');
INSERT INTO TEST (C1,C2,C3) VALUES('E1','P2','2002');
INSERT INTO TEST (C1,C2,C3) VALUES('E2','P2','2002');
INSERT INTO TEST (C1,C2,C3) VALUES('E3','P1','2001');
INSERT INTO TEST (C1,C2,C3) VALUES('E3','P2','1999');
INSERT INTO TEST (C1,C2,C3) VALUES('E3','P2','2002');
INSERT INTO TEST (C1,C2,C3) VALUES('E3','P6','2007') ;

You may consider using XMLSERIALIZE and XMLAGG functions to aggregate your data. A first "look-an-feel" SQL would be:

Select all

SELECT C1
,CAST(
XMLSERIALIZE(
XMLAGG(
XMLTEXT(C2 CONCAT ' ')
)
AS CLOB(500) EXCLUDING XMLDECLARATION
)
AS VARCHAR(500)
)
AS C2
FROM TEST
GROUP BY C1

The resulting data from the TEST table:

Select all

---------+---------+---------+---------+---------+---------
C1 C2
---------+---------+---------+---------+---------+---------
E1 P1 P2
E2 P2
E3 P1 P2 P2 P6
DSNE610I NUMBER OF ROWS DISPLAYED IS 3
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

To add the project year and column data delimiters, enhance the SQL similar to this:

Select all

SELECT C1 CONCAT '|' CONCAT
STRIP(
CAST(
XMLSERIALIZE( XMLAGG(
XMLTEXT(C2 CONCAT '|' CONCAT C3 CONCAT '|')
ORDER BY C3 ASC)
AS CLOB(500) EXCLUDING XMLDECLARATION)
AS VARCHAR(500) )
,T,'|')
FROM TEST
GROUP BY C1
;
---------+---------+---------+---------+---------+---------
E1|P1|2000|P2|2002
E2|P2|2002
E3|P2|1999|P1|2001|P2|2002|P6|2007
DSNE610I NUMBER OF ROWS DISPLAYED IS 3
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Hope this helps.

Best regards,
alexm

IBM Mainframe Forum

Specific SQL requirement

Specific SQL requirement

Re: Specific SQL requirement