I event don't know the name of what I am trying to do to google it.. I'm using sql2008 and here's the problem: 3 tables must be come together to form the table I wrote below..
This is just a pivot. It should really be handled by your user interface (Access, Crystal, Excel, .Net, etc) as it is inherently a display issue. But if you need to do it with SQL you can use the CASE statement, and there is an excellent example of how to do it in Books Online.
If it's not practically useful, then it's practically useless.
pivot didnt work because it can ony accept aggregate function. I also need money indormation (euro, usd, etc...) I did run pivot but could not append USD nor EURO to it. that makes 2 columns of pivot information. price and money information. also pivot did not accept string functions.
product count, sales point count, and even money count (usd, eoru, rouble, etc) are not pre-defined.
also I cannot solve it with case function because named are actually unknown and entered by customer to database. Even money names are not known. it can be usd, eoru or even japaneese yen. basically everything in this query must be obtained from tables dynamically.