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. Another way is using the CONCAT function. SQL Server does not support the operator for concatenation. Here’s an example using the + symbol: SELECT John + Smith AS fullname FROM dual Result: FULLNAME. If your actual query had 1000s of rows, a single MySQL would not have been practical. String concatenation in SQL Server is done using the + symbol or the CONCAT function. SELECT prev: prev + total 1 as column1, prev / quantity 1 as value from table but I don't get the expected results. 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. that is to say, to be able to obtain the sum of the total, (total 1 + previous total sum) to then do the division with quantity 1. 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 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 |