SQL split values to multiple columns

Question

I have table:

+---------------+------------------------------+-----+------+
| id_order      | id_product                   | qty | size |
+---------------+------------------------------+-----+------+
| ORD-0413-17-1 | PRD-0408-17-2,PRD-0412-17-11 | 2,3 | M,S  |
+---------------+------------------------------+-----+------+

I would like to have an output like this:

+---------------+---------------+-----+-------+
| id_order      | id_product     | qty | size |
+---------------+----------------+-----+------+
| ORD-0413-17-1 | PRD-0408-17-2  |  2  |  M   |
| ORD-0413-17-1 | PRD-0412-17-11 |  3  |  S   |
+---------------+----------------+-----+------+

How I can do this?

It's possible to do in PHP, but this really seems to be a bad practice regardaring to how the database is built. — bestprogrammerintheworld
– bestprogrammerintheworld, Commented Apr 13, 2017 at 7:54
You can do this in MySQL, but your data is screaming out for normalization. Don't store CSV data like this. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 13, 2017 at 7:54
and how can I build a good database structure? @TimBiegeleisen — Imam Ali Mustofa
– Imam Ali Mustofa, Commented Apr 13, 2017 at 8:01
Just store the data as you have in your expected output. If you already recognized this, and just need to get from point A to point B, then let us know. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 13, 2017 at 8:02

Strawberry · Accepted Answer · 2017-04-13 08:36:27Z

2

Here's one way in which to build a normalized 'result' from your 'data'... I'm using a simple utility table of integers (0-9), but you could just use a bunch of UNIONs instead.

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id_order VARCHAR(20) NOT NULL
,id_product VARCHAR(255) NOT NULL
,qty VARCHAR(30) NOT NULL
,size VARCHAR(30) NOT NULL
);

INSERT INTO my_table VALUES
('ORD-0413-17-1','PRD-0408-17-2,PRD-0412-17-11','2,3','M,S');

DROP TABLE IF EXISTS ints;

CREATE TABLE ints(i INT NOT NULL PRIMARY KEY);

INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT DISTINCT id_order
              , SUBSTRING_INDEX(SUBSTRING_INDEX(id_product,',',i+1),',',-1) id_product
              , SUBSTRING_INDEX(SUBSTRING_INDEX(qty,',',i+1),',',-1) qty
              , SUBSTRING_INDEX(SUBSTRING_INDEX(size,',',i+1),',',-1) size
           FROM my_table,ints;


    id_order      id_product    qty size
    ORD-0413-17-1 PRD-0408-17-2   2 M
    ORD-0413-17-1 PRD-0412-17-11  3 S

answered Apr 13, 2017 at 8:36

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nineoclick Over a year ago

This is an elegant yet smoothly solution and can be tuned well with php.

Strawberry Over a year ago

@Nineoclick I disagree. PHP affords no benefit here that I can see.

Nineoclick Over a year ago

the afford I see is that before querying "SELECT DISTINCT..." is possible to inspect id_product to find the max sizeof of splitted-by-comma id_product field and then insert into "ints" as many ints as sizeof value for the cross join.

Strawberry Over a year ago

@Nineoclick OK, fair point. The count can be performed in MySQL, but the logic for adjusting the query based on the count would require either a sproc or, as you say, a bit of application level code.

Imam Ali Mustofa · Accepted Answer · 2017-04-13 09:13:02Z

in my case, am using method in class php, this is the method :

public function user_checkout($params){

    $date = new DateTime(date('Y-m-d'));
    $date->modify('+3 day');

    $query = "SELECT * FROM cart WHERE id_session='".$params['id_user']."'";
    $sql = $this->db->query($query);
    $data1 = array();
    $data2 = array();
    $data3 = array();
    while($result = $sql->fetch_assoc()){

        $data1[] = $result['id_product'];
        $data2[] = $result['qty'];
        $data3[] = $result['size'];

    }

    $data_insert = array(
        "id_order" => $params['id_order'],
        "id_product" => implode(',', $data1),
        "id_user" => $params['id_user'],
        "qty" => implode(',', $data2),
        "size" => implode(',', $data3),
        "account_name" => $params['name_of_account'],
        "account_number" => $params['no_rekening'],
        "amount" => $params['amount'],
        "total_price" => $params['total_price'],
        "out_of_date" => $date->format('Y-m-d'),
        "order_date" => date('Y-m-d')
    );

    $insert = "INSERT INTO `order_product`(`id_order`, `id_product`, `id_user`, `qty`,`size`, `account_name`, `account_number`, `amout`, `total_price`, `out_of_date`, `order_date`, `status`) VALUES ('".$data_insert['id_order']."','".$data_insert['id_product']."','".$data_insert['id_user']."','".$data_insert['qty']."','".$data_insert['size']."','".$data_insert['account_name']."','".$data_insert['account_number']."','".$data_insert['amount']."','".$data_insert['total_price']."','".$data_insert['out_of_date']."','".$data_insert['order_date']."',0)";
    $sql_insert = $this->db->query($insert);

    $delete = "DELETE FROM cart WHERE id_session = '".$params['id_user']."'";
    $sql_delete = $this->db->query($delete);

    return true;

}

Collectives™ on Stack Overflow

SQL split values to multiple columns

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related