Then, you have a table with the products and categories combinations individually written. Instead of running the 3 INSERT queries by hand, you could echo the 3 INSERT queries to a text file and execute it as a script.
If your actual query had 1000s of rows, a single MySQL would not have been practical. In MySQL 8. I could have taken it to another level and concatenated all the queries into a single query, but the SQL would have been insanely long. To be honest, I think SQL Server can perform all of this in a single pivot query without a handmade temp table. Did the prodcat table populate properly? mysql> select * from prodcat
Mysql> insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL Mysql> insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL Let me run each line by hand mysql> insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL | insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL | | insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL | | insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL | > replace(categories,',',' union select '),') A where cat IS NOT NULL ') ProdCatQueries from prod Here it is executed mysql> select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ', Replace(categories,',',' union select '),') A where cat IS NOT NULL ') ProdCatQueries from prod Syntax CONCAT ( expression1, expression2, expression3 .) Parameter Values Technical Details Works in: From MySQL 4. Note: Also look at the CONCATWS () function. Here it is: select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ', Definition and Usage The CONCAT () function adds two or more expressions together. OK, you need query to put together each product_id with each category.
#Mysql concat from select code
If you are willing to store the results in a temp table, I got just the thing.įirst, here is the code to use you sample data in a table called prod and a temp table called prodcat to hold the results you are looking for. What you are looking for is the inverse of a GROUP BY aggregate query using the GROUP_CONCAT.
0 kommentar(er)
