For a little less than two years ago I published a tip about how to split a column over several rows. Now it is time to write about how to gather information from several rows into one row. Now you may think that this is just about using GROUP BY and I will admit that GROUP BY is very useful especially in combination with CASE, but this is a subject I might write about another time.

My idea is to use LEFT JOIN for the purpose. Just to illustrate the principle I will use an example you can execute on any DB2 for z/OS. The task is to display a single row for each table containing both the table name and the name of the columns in the primary key. This can be done in the following way:

select substr(t.name, 1, 18) table
     , char(strip(c1.name) !! ' '
     !! strip(value(c2.name, '')) !! ' '
     !! strip(value(c3.name, '')) !! ' '
     !! strip(value(c4.name, '')) !! ' '
     !! strip(value(c5.name, '')) !! ' ') primary_key_columns
from sysibm.systables t
     left join sysibm.syscolumns c1 on c1.keyseq = 1
     and c1.tbname = t.name and c1.tbcreator = t.creator
     left join sysibm.syscolumns c2 on c2.keyseq = 2
     and c2.tbname = t.name and c2.tbcreator = t.creator
     left join sysibm.syscolumns c3 on c3.keyseq = 3
     and c3.tbname = t.name and c3.tbcreator = t.creator
     left join sysibm.syscolumns c4 on c4.keyseq = 4
     and c4.tbname = t.name and c4.tbcreator = t.creator
     left join sysibm.syscolumns c5 on c5.keyseq = 5
     and c5.tbname = t.name and c5.tbcreator = t.creator
where t.creator = 'SYSIBM'
  and t.name like 'SYS%'
  and t.type = 'T'
  and t.keycolumns > 0
order by t.name

Because of the fact that the number of columns in the primary key is not known in advance a LEFT JOIN is an obvious choise. To make the solution work for similar tasks the table containing the rows to be joined into one row must contain a column that identifies each individual row. In the example it is the keyseq column. Furthermore you must be able to identify each individual row in the SQL. In the example this is acheived using the constants 1, 2, 3, 4 and 5. In the example the "on c2.keyseq = 2" may be replaced by the expression "on c2.keyseq = c1.keyseq + 1" and so on. The most important is that each row is identifiable.

The above approach has the big disadvantage of limiting the number of rows to be joined into one. It is the number of specified LEFT JOINS that determines the maximum number of rows to join. You can signal that there were more rows to join by adding an extra left join and add the expression CASE C6.KEYSEQ WHEN 6 THEN '>>' ELSE '' END to the concatenation.

Some of my faithful readers may have noticed that I am very enthusiastic about recursive solutions. This tip calls for a recursive solution, but I have not yet had the need to implement it or the time to develop it. If you can develop a recursive solution i SQL to carry out the above task I will be more than happy to hear about it.