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

First Post

I'm Poorna Sanjeewa. 26 years old (At the time of publishing this post) Software Engineer from Sri Lanka. I began my career as a trainee RPG programmer in 2012. From there to today  in my RPGLE career as a programmer, I have tried, I have done, I have learned and been learning so many things.

From today onward I will share what I have learned so far, whatever the things I am learning and things I am trying to do using RPGLE, SQL, JAVA and more. I hope this IBM i tech blog will be able add something new to your knowledge. Also please be kind enough to point out if I make any mistakes in my codings.

So that is it for the first post. In future there will be less talking and more coding in this blog.