Hi,
I'm doing a report with a group of queries but right now is very slow, so I need to do it faster. These are not the real tables but will help:
The report needs to show the total products for every combination of ADDRESS and PRODUCT_TYPE. Assume these are the tables:
ADDRESS: ADDRESS_ID, ADDRESS_NAME
PRODUCT_TYPE: PRODUCT_TYPE_CODE, PRODUCT_TYPE_NAME
ORDER: ORDER_ID, DATE, ORDER_STATUS
ORDER_LINE: ORDER_ID, ORDER_PRODUCT_TYPE, PRODUCT_TOTAL
(This is an special table to handle the stock)
STOCK_INFO: STOCK_ACTUAL, DATE_UPDATED
This is what I'm doing in code (asp):
1. Retrieve all the address (and put it in array)
2. Retrieve all the product types (and put it in array)
3. Using double "FOR" I build the query for every combination of Address and ProductType
This is still slow (and is even better than before) and I would like to put everything in just 1 query and get this data ready to show in HTML
Address Product Type 1 Product Type2 Product Type3
Address1 TotProdType11 TotProdType21 TotProdType31
Address2 TotProdType12 TotProdType22 TotProdType32
.....
I'll really appreciate any help. And also any better idea to do these is welcome (is just I don't have to much knowledge in very complex queries)
Thanks in advance
Moving to Transact-SQL forum...|||I don't see any relationship between Address table and the Product_Type at all. How is it related ?|||use a CROSS JOIN in SQL server if you want a combination of all products and addresses.
eg
select a.address, Address p.Product Type from address a cross join product_type p
Note that if you are wanting a cartesian product here, you should specify no join criteria, as you want every address and product combination. That should be much quicker than doing it in client side code. However, you will need some kind of join to get the totals for each product, as a cross joins blindly combines all rows from 1 table to all the rows from another. I need further clarification here.
You will then have to turn the results into a pivot table. In SQL 2005, use the PIVOT function, in SQL 2003 and earlier, you will need to use a case statement:
SELECT a.address,
CASE
WHEN p.Product_type = 'Product A' -- whatever first product type is
THEN ..... -- your code, I think from your example you want a sum() here
WHEN p.Product_type = 'Product A' --
THEN
etc
END
from.......
Hope that helps
from address a cross join product_type p
GROUP BY a.address
