Combine 2 cols same table side by side

[jjjcr]

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 March 25, 2018 by jjjcr

[Barand]

SELECT CONCAT(food_name, ' - ', qty) as food_qty ...

[jjjcr]

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?

[Barand]

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

[jjjcr]

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 March 25, 2018 by jjjcr

[Barand]

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.

[jjjcr]

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 March 25, 2018 by jjjcr

[benanamen]

Your problem is that you are improperly storing your data.

[Barand]

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.

[Barand]

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";