Step by Step guide to create SQL User Defined Functions(UDF) - How to concatenate multiple rows into a single row using SQL

How to concatenate multiple rows into a single row using SQL

To do this, there is GROUP_CONCAT function in MySQL. But unfortunately we do not have that function in DB2. So what we can do is create our own SQL function.  

One day my superior showed me a simple SQL query he executed. That query was listed all the claim types that each policy holder had. The result set was as below.

Plcy_Num	Plcy_Type
SSD0113	AA
SSD0113	MM
SSD1171	AA
SSD1171	MM
SSD1998	MM
SSD3084	SS
SSD5818	MM
SSD6722	MM
SSD7843	AA
SSD7843	MM

The he had executed below query.

select Plcy_Num, Plcy_Type   from  Library.claim_table     group by Plcy_Num, Plcy_Type     order by Plcy_Num, Plcy_Type

My superior asked me “Poorna, is there any way to get all the claim types into a single row against particular policy number?” I said “Yes. That should be possible. Give me few minutes I’ll try and let you know”

Then I googled and found that I can achieve this task by creating a simple SQL function.  

1^st Step

Type STRSQL command in the command line. (The only command that I type most number of times in a day)

2^nd Step

Let’s create the SQL function

CREATE or REPLACE FUNCTION QGPL/sltr  (              POL_NUM CHAR(7) )                                      RETURNS VARCHAR(40)                                    LANGUAGE SQL                                           NOT DETERMINISTIC                                      READS SQL DATA                                         CALLED ON NULL INPUT                                   DISALLOW PARALLEL                                       BEGIN                                                    DECLARE ReturnVal VARCHAR(40) NOT NULL DEFAULT ' ';         FOR CsrC1 AS C1 CURSOR                                     FOR SELECT Plcy_Num, Plcy_Type       FROM Library.claim_table        WHERE Plcy_Num = POL_NUM                                        GROUP BY Plcy_Num, Plcy_Type        ORDER BY Plcy_Num, Plcy_Type                             DO SET ReturnVal = ReturnVal Concat CsrC1.Plcy_Type ||' ';   END FOR;                                                 RETURN LTRIM(ReturnVal);   end

I prefer creating commonly use SQL functions, commands in QGPL.

This function gets 7-character value (policy number) as input put parameter and returns 40-character value (All the claim types separated by space).
Function fills the data into “ReturnVal” variable by using a SQL cursor to reading all the claim types a particular policy has.

3^rd Step
Let’s try to select data using the created function.

select Plcy_Num, sltr(Plcy_Num) claim_Types     from Library.claim_table     GROUP BY Plcy_Num                         ORDER BY Plcy_Num

 Result set

PLCY_NUM	CLAIM_TYPES
SSD0113	AP MT
SSD1171	AP MT
SSD1998	MT
SSD3084	SU
SSD5818	MT
SSD6722	MT
SSD7843	AP MT

This Function is SQL scalar function. Means that the function is written exclusively in SQL and returns a scalar value. My next post will show how to create an external scalar SQL function. Which means the function written in a different language (RPGLE, JAVA, C, etc.) and that program referenced by a SQL function.



References:

• http://www.itjungle.com/fhg/fhg100808-story02.html
• http://www.code400.com/forum/forum/iseries-programming-languages/sql/6616-group_concat
• https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0003493.html