将多个列组合成一个长列

Question

I've looked on the boards and googled a bunch, but I cant find a scenario that fits mine. I have 4 columns

+--------+------+------+
| empnum | appn | rate |
+--------+------+------+
|   13   | 1111 |12.34 |
+--------+------+------+
|   13   | 2222 |14.44 |
+--------+------+------+
|   13   | 3333 |15.62 |
+--------+------+------+
|   13   | 4444 |16.12 |
+--------+------+------+

each column has the same employee number but different information. I'm trying to combine these 4 columns into 1. The only thing I could find was concatenation. But that won't work for me because these need to go into an array so I can build it in datatables. I would like it to look like this.

+------+-----+-----+-----+-----+-----+-----+-----+-----+
|empnum|appn1|rate1|appn2|rate2|appn3|rate3|appn4|rate4|
+------+-----+-----+-----+-----+-----+-----+-----+-----+
|  13  |1111 |12.34|2222 |14.44|3333 |15.62|4444 |16.12|
+------+-----+-----+-----+-----+-----+-----+-----+-----+

As of a week ago all of this information was coming from a flat file. A coworker created a relational table and that's when my brain exploded. We are working on Db2 and I tried to do this in php using a foreach to loop through the array and look for the empnum but I would get the same appn1 for every person. I'd like to do it sql I just can't think of how.

Answer 1

What you want is to Pivot your data. DB2 doesn't have a PIVOT function, but you can use DECODE to achieve this. Here's some examples in another question.

The result set would look something like:

+------+-----+-----+-----+-----+-----+-----+
|empnum|appn1111|appn2222|appn3333|appn4444|
+------+--------+--------+--------+--------+
|  13  |12.34   |14.44   |15.62   |16.12   |
+------+--------+--------+--------+--------+