Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2015
    Posts
    5

    Unanswered: combining multiple columns into one long column

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

    Code:
       +--------+------+------+
        | 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.

    Code:
        +------+-----+-----+-----+-----+-----+-----+-----+-----+
        |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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5

  3. #3
    Join Date
    Feb 2015
    Posts
    5
    I cant concat because I need to put these into an array. I could substr as a last resort.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    How do you deal with this if there are more than four rows? SQL does not have queries that can produce different amounts of column. How you consume the data is up to you. But you need to work with how you can get the data.

    Andy

  5. #5
    Join Date
    Feb 2015
    Posts
    5
    Exactly. That is what I am trying to do, manipulate the way I get the data. I've looked at pivots, and recursion. I can't get recursion to work so I was thinking about a stored procedure or a temporary table but the fundamental problem is how do I query and get everything from emp into a place that I can use it.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    You have two options.

    1) you consume the data from the table as it exists. As you read through the rows, you build your array.
    2) you use listagg then you parse the resulting column to build your array.

    Andy

  7. #7
    Join Date
    May 2010
    Location
    India
    Posts
    82
    Provided Answers: 2
    Try array_agg function. See the following link for details.

    http://www.ibm.com/support/knowledge.../r0050494.html

    Satya..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •