Jump to content
jjjcr

Combine 2 cols same table side by side

Recommended Posts

Hi guys, how do i join two columns as one column side by side?

 

What I mean is like this: (columns in same table, table name as foodx)

 

foodx

---

Food_Name  Food_Qty

Chicken         3

Hotdog          5

...

.... rest of data continues

 

Desired output:

 

Food_Name_Qty

 

Chicken - 3

Hotdog - 5

 

 

Thanks in advance for your help.

Edited by jjjcr

Share this post


Link to post
Share on other sites

Hi Sen, thank you so much for you reply.

 

I tried your solution but the output is like this:

 

Chicken

Hotdog         

- 3

5

 

It it possible to have it side by side?

Share this post


Link to post
Share on other sites

Hi Newbie, what is your current code that is giving that output

Share this post


Link to post
Share on other sites

This is what i got from your answer:

 

SELECT CONCAT(food_name, ' - ', food_qty) as food_name_qty from foodx

 

Forgot to tell that in one row there is multiple lines. What i mean:

 

id  Food_Name  Food_Qty  

 1  Chicken         3 

     Hotdog           5

 

so basically, id 1 has two lines

 

Concat is good if one row has one line, but how to combine two columns if there are multiple lines? and combine them side by side.

 

Thanks again.

Edited by jjjcr

Share this post


Link to post
Share on other sites

My assumption was that your table looks something like

 

mysql> select * from foodx;
+----+-----------+------+
| id | food_name | qty  |
+----+-----------+------+
|  1 | Chicken   |    3 |
|  2 | Hotdog    |    5 |
|  3 | Burger    |    4 |
+----+-----------+------+

 

So what does it really look like and how exactly do you want the output to look?

 

And you still haven't shown us the code that you are attempting.

Share this post


Link to post
Share on other sites

I really appreciate your reply:

 

My table looks like this:

 

| id | food_name | food_qty  |
  1     Chicken       3

         Hotdog        4

--------------------------------

  2     Egg             1

         Bun             1

         Onions        3

 

So basically, 1 id has multiple lines on it

 

I tried this:

 

SELECT
    replace(replace(food_name, char(13), ' '), char(10), ' '),
    replace(replace(food_qty, char(13), ' '), char(10), ' '),    
    CONCAT(food_name,' ',food_qty) as food_name_qty
FROM foodx

 

The reason I tried to replace the line feed because i was hoping that the concat would replace the new line and the values will be side by side but it's not.

 

I also tried this:

SELECT CONCAT_WS(' ',COALESCE(food_name,''),COALESCE(food_qty,'')) AS food_name_qty FROM foodx

 

It doesn't work, also.

 

I just want the output to display side by side.

 

food_name_qty:

  Chicken - 3 ( the food name has the quantity on the same row)

  Hotdog - 4

 

Thanks, I really need your help.

 

if this would not be possible, maybe i would just create a separate table with the pre-formatted string so i will I would have any problem for retrieval of data when it's exported to CSV.

 

 
  

Edited by jjjcr

Share this post


Link to post
Share on other sites

So are you saying that "chicken" and "hotdog" are in the same field, separated by a CR? And then in the qty column you have "3<CR>4". In other words, the table is really

 

 

| id | food_name                   | food_qty     |
+----+-----------------------------+--------------+
| 1  |  Chicken<cr>Hotdog          |  3<cr>4      |
| 2  |  Egg<cr>Bun<cr>Onions       |  1<cr>1<cr>3 |

 

You might design a spreadsheet like that but it not a valid database table design. It Should be

 

id | food_name | food_qty  |
 +--+-----------+-----------+
  1 | Chicken   |    3      |
  1 | Hotdog    |    4      |
  2 | Egg       |    1      |
  2 | Bun       |    1      |
  2 | Onions    |    3      |

 

You need to redesign your data model.

Share this post


Link to post
Share on other sites

Just for fun

 

/*****************************************************
*  If I were correct about your table looking like
*  the example below, this code will create a
*  correctly normalized table for you and transfer
*  your current data (foodx) to the new table (foodz)
*******************************************************
 
    | id | food_name                   | food_qty     |
    +----+-----------------------------+--------------+
    | 1  |  Chicken<cr>Hotdog          |  3<cr>4      |
    | 2  |  Egg<cr>Bun<cr>Onions       |  1<cr>1<cr>3 |
    
*******************************************************/

$db = mypdo('test');    // connect to database "test"

$db->exec("CREATE TABLE IF NOT EXISTS foodz (
           id int not null,
           food varchar(30),
           qty int,
           PRIMARY KEY (id,food)
           )");
           
$res = $db->query("SELECT
                       id
                     , food_name
                     , food_qty
                   FROM foodx");
                   
$zInsert = $db->prepare("INSERT INTO foodz (id,food,qty) VALUES (?,?,?)");

foreach ($res as $r) {
    $arr_food = explode("\n", $r['food_name']);
    $arr_qty  = explode("\n", $r['food_qty']);
    foreach ($arr_food as $k=>$food) {
        $qty = $arr_qty[$k];
        $zInsert->execute( [ $r['id'], $food, $qty ] );
    }
}

/**
*  Data transferred.
*  Query the new table to check
*/

$res = $db->query("SELECT id
                     , food
                     , qty
                   FROM foodz");
echo "<pre>\n";
foreach ($res as $r) {
    vprintf("| %d | %-30s | %3d\n", $r);
}
echo "</pre>\n";
  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.