Crosstab Query/report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Crosstab Query/report

Author

Topic

Halim1973
Starting Member

2 Posts

Posted - 2011-07-11 : 19:31:58

I have a table myTable with the following fields: categoryID,Description, SiteID, QtySold, TotalPrice.

I use this query to get a report that shows totalQtySold and Total TotalPrice per category per site.

SELECT category,
SUM(CASE WHEN T.Site = 1 THEN QtySold END) AS [Site 1 Qty Sold],
SUM(CASE WHEN T.Site = 1 THEN TotalPrice END) AS [Site 1 total price],
SUM(CASE WHEN T.Site = 2 THEN QtySold END) AS [Site 2 Qty Sold],
SUM(CASE WHEN T.Site = 2 THEN TotalPrice END) AS [Site 2 total price]
FROM myTable
GROUP BY category

The result of this query looks like:.
Site 1 Site 2
Category Description Qty Sold Total price Qty Sold Total price
1 General Items 10 25.00 30 34.00
2 Food 20 17.00 45 52.00
3 Etc…
4

This query works fine when I know how many sites I have ahead of time but it doesn't help me because The number of sites change all the time. it varies from 1 to 10 or more. is there way I can get a dynamic query that give me similar result set even if I have a variable number of sites.
my sites are in a table called Sites which has a SiteID and SiteName as fields.
Any help would be greatly appreciated.
Thank you,
-Halim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-11 : 21:26:53

you can use Dynamic SQL to do it

http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-12 : 10:24:44

or http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Halim1973
Starting Member

2 Posts

Posted - 2011-07-12 : 23:54:19

I started building a stored procedure to get the following string based on the number of sites:

SUM(CASE WHEN T.Site = 1 THEN QtySold END) AS [Site 1 Qty Sold],
SUM(CASE WHEN T.Site = 1 THEN TotalPrice END) AS [Site 1 total price],
SUM(CASE WHEN T.Site = 2 THEN QtySold END) AS [Site 2 Qty Sold],
SUM(CASE WHEN T.Site = 2 THEN TotalPrice END) AS [Site 2 total price]

I built the variable using the following stored procedure:
Lets assume it's called: SP1
Declare SITECursor CURSOR FOR Select SiteID,SiteName from Site
Declare @SiteID VarChar(10)
Declare @SiteName VarChar(50)

SELECT @SQL= ''''
Open SiteCursor
Fetch SiteCursor into @SiteID, @SiteName
while @@FETCH_STATUS = 0
begin
SELECT @SQL=@SQL+ ''SUM(CASE WHEN I.SiteID =''+ @SiteID +'' THEN U.[Quantity Sold] END) AS [''+ @SiteName +'' Qty Sold],''+
''SUM(CASE WHEN I.SiteID =''+ @SiteID +'' THEN U.[Non-Taxable Income] +U.[Taxable Income] + U.[TaxFreeSales] END)
AS [''+ @SiteName +''total Direct Sale],''

Fetch SiteCursor into @SiteID, @SiteName
end
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)

After all that I used another stored procedure to get the result set I want in a crosstab form.
the query is:

Exec SP1 @SQL Output
Select Category, @SQL
From mytable
Group by Category

The result set was not what I was looking for. it had
Category, (No Column name)
1, SUM(CASE WHEN I.SiteID =1 THEN U.[Quantity Sold] END) ...etc,...
2, SUM(CASE WHEN I.SiteID =2 THEN U.[Quantity Sold] END) ...etc,...
etc..

Basically, It was not passing the @SQL as variable, it was using it as a quoted string.
I'm sure I'm missing something simple. Any help would be appreciated.
Thank you,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources