Combining multiple columns into one long column

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.

u need to convert row data into column data check this link : http://stackoverflow.com/questions/14834290/mysql-query-to-dynamically-convert-rows-to-columns — MAK, Jun 10 '16 at 13:35
Your google problems might be that you are using the term "column" when you mean "row" — Hogan, Jun 10 '16 at 18:59

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

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   |
+------+--------+--------+--------+--------+

edited May 23 '17 at 12:15

Community

1
1

answered Jun 10 '16 at 13:39

SteveR

199
7

read about the pivot. but if i did the above I could get appn into an array element – moe Jun 10 '16 at 13:59

score 0 · Answer 2 · answered Jun 10 '16 at 19:01

You could also do this with LISTAGG to get a comma separated list.

Like this:

SELECT empnum, LISTAGG(TO_CHAR(appn) || ' - ' || TO_CHAR(rate), ', ') as a_list_4_u
FROM mytable

The result should look like this

+--------+--------------------------------------------------------+
| empnum |                                                      2 |
+--------+--------------------------------------------------------+
|   13   | 1111 - 12.34, 2222 - 14.44, 3333 - 15.62, 4444 - 16.12 |
+--------+--------------------------------------------------------+

Combining multiple columns into one long column

2 Answers2