By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

Crosstab queries

P: n/a
Dear friends,

I wonder if exist a way to make crosstab queries in SQL Server
like those in Access without "external" programming. Does the
SQL Server supports the "TRANSFORM" SQL-extension?

Thanks in advance, Sotiris.


Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Sotiris Rentoulis (re*******@hotmail.com) writes:
I wonder if exist a way to make crosstab queries in SQL Server
like those in Access without "external" programming. Does the
SQL Server supports the "TRANSFORM" SQL-extension?


No.

There is no particular support for pivot tables in SQL 2000. SQL 2005,
currently in beta, comes with a PIVOT operator. It still only supports
static pivot tables.

Here is a simple example of a static crosstab in SQL 2000:

SELECT product,
Q1 = SUM(CASE datepart(month, salesdate) / 3 WHEN 0 THEN amt END),
Q2 = SUM(CASE datepart(month, salesdate) / 3 WHEN 1 THEN amt END),
Q3 = SUM(CASE datepart(month, salesdate) / 3 WHEN 2 THEN amt END),
Q4 = SUM(CASE datepart(month, salesdate) / 3 WHEN 3 THEN amt END)
FROM sales
GROUP BY product

Dynamic crosstabs requires you to write dynamic SQL. Note that dynamic
crosstabs, how useful they be, do not really fit into the relational
model. A popular tool for dynamic crosstab is RAC, see
http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.