[dba-SQLServer] crosstabs in sql

 SQL Server Express 2005  provides several new analytic functions
RANK, DENSE_RANK, NTILE, ROW_NUMBER, to
name a few, as well as quite a few other new language features, like
PIVOT and UNPIVOT, OUTER APPLY and CROSS APPLY.
These will probably help with crosstabs.

Here is a book chapter excerpt that might explain.
http://www.ftponline.com/books/chapters/default_pf.asp?isbn=0321180593

A First Look at SQL Server 2005 for Developers
by Bob Beauchemin, Niels Berglund, Dan Sullivan
Addison-Wesley Professional
ISBN: 0321180593  

John Maxwell @ London City wrote:

>Hello Susan,
>
>afraid crosstabs not so straight forward in SQL server.
>
>They are reasonably straight forward to do if you are using fixed column
>headings,
>a little more involved if you want a 'dynamic crosstab' 
>
>Example copied from SQL server 2000 Bible:
>(I am a newby with SQL server and just happen to be reading up on Crosstabs
>and recursive select variables,
>so anyone please jump in to point out any errors / inefficiencies / better
>ways to mimic access Crosstabs)
>
>1)Fixed Column
>
>Select Y,
>Sum (Case X when 'A' Then data Else 0 End) AS A
>Sum (Case X when 'B' Then data Else 0 End) AS B
>Sum (Case X when 'C' Then data Else 0 End) AS C
>Sum (Case X when 'D' Then data Else 0 End) AS D
>Sum (Data) as Total
>>From RawData
>Group By Y
>Order By Y
>
>2)Dynamic Crosstab
>Instead of 'hard coding' the columns of your crosstab the script below
>determines them via a 'recursive select variable'
>
>Use TempDB
>
>Declare @XColumns NVarChar(1024)
>Set @XColumns = ''
>Select @XColumns + 'Sum(Case X
>			  When ''' + [a].[Column] + ''' Then Data
>		        Else 0
>			  End ) AS '
>			+ [a].[Column] + ','
>			From
>			(Select Distinct X as [Column] 
>			from Raw Data) as a
>SET @XColumns = Select Y,' + XColumns + ' Sum(Data) as Total From Raw Data
>Group By Y Order By Y'
>
>EXEC sp_executesql @Xcolumns
>
>
>I hope I have not confused things for you let if you email your Access SQL
>through happy to give a go converting if required.
>
>Regards
>
>john
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Klos,
>Susan
>Sent: 11 August 2004 22:13
>To: 'dba-sqlserver at databaseadvisors.com'
>Subject: [dba-SQLServer] crosstabs in sql
>
>
>How do you create a crosstab query in SQL Server.  I tried doing one in
>Access and copying the SQL into SQL Query Analyzer but I can't seem to get
>it right.  
>
> 
>
>Susan Klos
>
>Senior Database Analyst
>
>Evaluation and Reporting
>
>Florida Department of Education
>
>850-245-0708 
>
>sc 205-0708
>
> 
>

-- 
Marty Connelly
Victoria, B.C.
Canada