How to rotate SQL Table

There was very good customer from Australia. He always had interesting projects. One of this project was to show report on products sales. At start it looks like simple project, but there were two issues:
1) Customer had very big amounts of data in MS SQL.
2) He would like to show data grouped in columns, not rows.

First version of project does grouping in the columns at the C# code, but with such big amount of data it was crazy slow and takes a lot of memory. This is why grouping has been moved to the MS SQL side. Here is a beautiful script (sorry, but can't remember original source):



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE  sp_CrossTab3
  @table AS sysname,       -- Table to crosstab
  @onrows AS nvarchar(128),   -- Grouping key values (on rows)
  @onrowsalias AS sysname = NULL, -- Alias for grouping column
  @oncols AS nvarchar(128),   -- Destination columns (on columns)
  @sumcol AS sysname = NULL   -- Data cells
AS


DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)


SET @NEWLINE = CHAR(10)
SET @sql = 'set nocount on ' + @newline + 'SELECT' + @NEWLINE + ' ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END


CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)


DECLARE @keyssql AS varchar(1000)
SET @keyssql = 
  'INSERT INTO #keys ' +
  'SELECT DISTINCT convert(nvarchar(23),' + @oncols + ',121)' +
  'FROM ' + @table


EXEC (@keyssql)


DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys


WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    'SUM(CASE convert(nvarchar(23),' + @oncols +
                     ',121)' + @NEWLINE +
    'WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
    'ELSE 0'                      + @NEWLINE +
    'END) AS [' + @key + ']'
  
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END
SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows


 PRINT @sql  + @NEWLINE -- For debug
SET NOCOUNT ON
EXEC (@sql)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 


And also data for testing:


if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[T30]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[T30]
GO


CREATE TABLE [dbo].[T30] (
[dates] [datetime] NOT NULL ,
[code] [int] NOT NULL ,
[name] [varchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,
[svalue] [int] NULL 
) ON [PRIMARY]
GO


INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2009-01-01',1 ,'Point1',110);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2009-01-01',2 ,'Point2',210);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2008-01-01',3 ,'Point3',310);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2008-01-01',4 ,'Point4',410);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2007-01-01',5 ,'Point5',510);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2007-01-01',1 ,'Point1',120);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2006-01-01',2 ,'Point2',220);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2006-01-01',3 ,'Point3',320);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2009-01-01',4 ,'Point4',420);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2009-01-01',5 ,'Point5',520);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2008-01-01',1 ,'Point1',130);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2008-01-01',2 ,'Point2',230);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2007-01-01',3 ,'Point3',330);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2007-01-01',4 ,'Point4',430);
INSERT INTO [dbo].[T30]([dates],[code],[name],[svalue]) VALUES ('2006-01-01',5 ,'Point5',530);

SELECT * FROM t30;
EXEC sp_crosstab3 't30','code,name','names','dates','svalue';

Hope this helps!