CrossTab Query

[draven07]

Need help!

As you can see, I'm working with a crosstab report that will display the product's "Total Sales" or "Quantity Sold" on a particular year in a monthly basis. Now my problem is, I can't figure it out how I can display the monthly total sales or quantity sold for a particular product on a grid. I've tried using WHILE statement on SQL but still I wasn't able to get the ideal output.

Here is my SQL Query:

DECLARE @Month int
SET @Month =1

WHILE (@Month <= 12)
BEGIN 
SELECT P.Name AS 'Product Name'
    ,SUM(SOIL.QuantityDisplay) AS 'Qty'
    ,SUM(UnitPrice) AS 'Amount'
FROM BASE_Product P
    INNER JOIN SO_SalesOrderInvoice_Line SOIL
    ON SOIL.ProductId = P.ProductId
WHERE DATEPART(m, SOIL.CreatedDateTime) = @Month
    AND DATEPART(YY, SOIL.CreatedDateTime) = 2013
GROUP BY P.Name, SOIL.QuantityDisplay, SOIL.UnitPrice
SET @Month += 1
END

Please enlighten me what's wrong!

Now my problem is, I can't figure it out how I can display the monthly total sales or quantity sold for a particular product on a grid.

I don't have database.

You can take a look at this:

http://www.fmsinc.com/tpapers/queries/crosstab.html

and this:

http://www.dba-sql-server.com/sql_server_tips/t_super_sql_456_crosstab_queries.htm