sql - Split columns in mySQL -

sql - Split columns in mySQL -

i have around 1,000 products in mysql table, each of appears have been duplicated (1 product sku , 1 product mpn, or manufacturer product number), shown below:

+--------------+------------+----------------+-----------------+------------------------+ | v_article_id | article_id | v_article_code | brand_domain_id | prefered_v_article_id | +--------------+------------+----------------+-----------------+------------------------+ | 11003 | 7043 | aa4011 | null | 11002 | | 11002 | 7043 | u-30g-bk | 101036 | null | +--------------+------------+----------------+-----------------+------------------------+

ideally combine 2 rows 1 row each product (as can see article_id same both rows), maintain brand_domain_id not null , split 2 separate vendor_article_code's 2 separate columns; sku (aa4011) , mpn (u-30g-bk), output looks this:

+------------+--------+----------+-----------------+ | article_id | sku | mpn | brand_domain_id | +------------+--------+----------+-----------------+ | 7043 | aa4011 | u-30g-bk | 101036 | +------------+--------+----------+-----------------+

i using next statement accomplish want (if single product based on article_id):

select article_id, case when v_article_code regexp '^aa' v_article_code end sku, case when v_article_code not regexp '^aa' v_article_code end mpn, brand_domain_id vendor_article article_id = 7043 +------------+--------+----------+-----------------+ | article_id | sku | mpn | brand_domain_id | +------------+--------+----------+-----------------+ | 7043 | null | u-30g-bk | null | +------------+--------+----------+-----------------+ | 7043 | aa4011 | null | 101036 | +------------+--------+----------+-----------------+

my question this: there way cutting downwards both rows become single row, no null elements , same article_id? also, there quick way in iterate through each of article_ids, such loop in php? know how insert output new table, i'm not sure best way approach solution.

any help appreciated.

yet implementation:

select a.article_id, a.v_article_code mku, b.v_article_code mpn, ifnull(a.brand_domain_id, b.brand_domain_id) brand_domain_id, ifnull(c.channel_sku, d.channel_sku) channel_sku vendor_article bring together vendor_article b on a.article_id = b.article_id , a.brand_domain_id not null , b.prefered_v_article_id not null left bring together vendor_article_channel c on a.v_article_id = c.vendor_article_id left bring together vendor_article_channel d on b.v_article_id = d.vendor_article_id;

fiddle

mysql sql