![]() 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. There is a way to dynamically generate the columns of a PIVOT table output. If your actual query had 1000s of rows, a single MySQL would not have been practical. 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 ', 4 Answers Sorted by: 13 +50 It is unclear to me what is a 'more elegant way'. Replace(categories,',',' union select '),') A where cat IS NOT NULL ') ProdCatQueries from prod Here it is: select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ', OK, you need query to put together each product_id with each category. ![]() 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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |