[dba-SQLServer] crosstabs in sql

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

 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


The contents of this email and any attachments are sent for the personal attention
of the addressee(s) only and may be confidential.  If you are not the intended
addressee, any use, disclosure or copying of this email and any attachments is
unauthorised - please notify the sender by return and delete the message.  Any
representations or commitments expressed in this email are subject to contract. 
 
ntl Group Limited