How to create a pivot table in SQL servers?

[cogivina]

In Access database, the below query should run:

TRANSFORM sum(Loss)
SELECT Year
FROM iTable
GROUP BY Year
PIVOT Month;

How should I write the code in MS SQL servers?

 

[SQLSister]

Depends are you using SQl server 2000 or 2005?

"NOTHING is more important in a database than integrity." ESquared

 

[checkai]

SQL Server 2005 has this notion or I have used dynamic SQL to create a pivot table. However the latter has limitations.

 

[cogivina]

Hi,
I'm not sure the questions, but this is the codes I used:
Sqt = " SELECT Year" -
& " Sum( Case Month when 1 then Loss else 0 end), " _
.......
& " Sum( Case Month when 12 then Loss else 0 end), " _
& " FROM " & iTable _
& " GROUP BY Year"

cnt.Open strconn

I just wonder if there is an easy way instead of using CASE statement. As you can see, I have to list all the months in my Case...Then...Else in the querry. In access, I only have to use 2 words...TRANSFORM...PIVOT.

Thanks.

 

[philhege]

It depends on your answer to SQLSister's question. SQL 2K5 has the functionality built in; 2K does not.

I have a couple of dynamic pivot scripts for 2K if that's what you need.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[yehong]

Hi Guys, I am also having same problem. I have a table that I need in a pivot form. Should I create a new post or just post my question here?

 

[cogivina]

Hi Phil,

I guess mine is SQL 2000. Could you post your pivot scripts for us?

Thanks.

 

[philhege]

OK.

USE --Your database here
if exists(select routine_name from information_schema.routines
where routine_name = 'gensp_CrossTab2')
DROP PROCEDURE gensp_CrossTab2

GO

create procedure dbo.gensp_CrossTab2 
	@SQL varchar(1000),	
	@PivotCol varchar(100),
	@Summaries varchar(100), 
	@GroupBy varchar(100),
	@OtherFields varchar(100) = Null
AS

set nocount on

declare @Vals varchar(5000);
set @Vals = '';

set @OtherFields = isNull(', ' + @OtherFields ,'')

exec ('SELECT Distinct convert(varchar(100),' + @PivotCol + ') as Pivot INTO ##Temp FROM (' +
	 @SQL + ') A')

select @Vals = @Vals + ', ' + 
    replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + Pivot + 
	''' THEN '),')[', ' END) as [' + Pivot )
from ##Temp order by Pivot

drop table ##Temp

set nocount off
print 'select ' + @GroupBy + @OtherFields + @Vals + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy
--uncomment the next line to run the statement
--exec ( 'select ' + @GroupBy + @OtherFields + @Vals + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy)

The SP is fairly self-explanatory. Definitely run it first with the EXEC commented out so you can see how it constructs the pivot SQL. Put the SQL statement in @SQL, the aggregate column in @Pivotcol, the rollup columns (comma-delimited, I believe) in @Summaries, GROUP BY columns in @GroupBy, and additional columns in @OtherFields.
Examine the results of the PRINT.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.