Jump to content

Combine 2 cols same table side by side


jjjcr

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.

Link to comment
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.

Link to comment
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.

Link to comment
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.

 

 
  

Link to comment
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.

Link to comment
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";
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.