crosstab query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

crosstab query

Author

Topic

van73
Starting Member

7 Posts

Posted - 2007-12-23 : 20:28:26

I have a table like this not showing primary columns and not relevant ones. This contains stock data whcih is updated every day.
Date Openingstock Received Delivered StockRemaining

I need to create a report as
columns all the dates selected by the user between two dates (number of columns is unfixed) showing date as Wed 1 Aug, Thurs 2 Aug, etc,.
Rows Openingstock, StockReceived, Delivered, StockRemaining, weeklyhighestInvenotory (fixed rows)

How to write sql for this? I am trying to use PIVOT but not able to use it for unfixed number of columns. There is no aggregate function to be performed. All the values are available in my table. Just need to format it as crosstab report and put it on web form.
I am using SQL server 2005 with crystal reports and developing using VS 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-24 : 01:40:09

I think the easier approach will be to take the summary data grouped by date as follows and use cross tabbed feature of crystal reports to get the report in required format.

SELECT SUM(OpeningStock) AS 'OpeningStock',
      SUM(Recieved) AS 'Recieved','
.........
FROM Table
WHERE Date BETWEEN @StartDate AND @EndDate
GROUP BY Date

for creating cross tabbed report refer this:-

[url]http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=181[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources