Creare una tabella dinamicamente!

USE tempdb;

CREATE TABLE [Table1] (
  [id] [int] IDENTITY (1, 1) NOT NULL ,
  [matricola] [tinyint] NOT NULL ,
  [anno_week] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
  [quantita] [tinyint] NOT NULL ,
   PRIMARY KEY  CLUSTERED 
  (
    [id]
  )  ON [PRIMARY] 
) ON [PRIMARY]
GO

INSERT INTO [tempdb].[dbo].[Table1]( [matricola], [anno_week], [quantita])
VALUES(1,200601,42)
INSERT INTO [tempdb].[dbo].[Table1]( [matricola], [anno_week], [quantita])
VALUES(2,200603,60)
INSERT INTO [tempdb].[dbo].[Table1]( [matricola], [anno_week], [quantita])
VALUES(1,200633,22)
INSERT INTO [tempdb].[dbo].[Table1]( [matricola], [anno_week], [quantita])
VALUES(1,200629,10)
INSERT INTO [tempdb].[dbo].[Table1]( [matricola], [anno_week], [quantita])
VALUES(1,200701,10)

SELECT * FROM dbo.Table1

Results
------------------
id        matricola anno_week quantita 
-------  ---------    --------- -------- 
1           1         200601    42
2           2         200603    60
3           1         200633    22
4           1         200629    10
5           1         200701    10

(5 row(s) affected)

>spero di essere stato chiaro....

USE tempdb;
GO

CREATE TABLE dbo.Table1 (
[id] int NOT NULL IDENTITY PRIMARY KEY,
matricola tinyint NOT NULL,
anno_week char(6),
quantita tinyint NOT NULL
);
GO

INSERT dbo.Table1 VALUES(1, '200601', 42);
INSERT dbo.Table1 VALUES(2, '200603', 60);
INSERT dbo.Table1 VALUES(1, '200633', 22);
INSERT dbo.Table1 VALUES(1, '200629', 10);
INSERT dbo.Table1 VALUES(1, '200701', 10);
GO

CREATE PROC dbo.sp_CrossTab
  @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)

-- Step 1: beginning of SQL string
SET @sql =
  'SELECT' + @NEWLINE + 
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END

-- Step 2: Storing Keys in a Temp Table
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql = 
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table

SET NOCOUNT ON
EXEC (@keyssql)
SET NOCOUNT OFF

-- Step 3: Middle Part of SQL String
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @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

-- Step 4: End of SQL String
SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

-- PRINT @sql  + @NEWLINE -- For debug
EXEC (@sql)
GO

EXEC dbo.sp_CrossTab
  @table       = 'Table1',
  @onrows      = 'matricola',
  @oncols      = 'SUBSTRING(anno_week, 1, 4)',
  @sumcol      = 'quantita';

/* Output:

matricola 2006        2007        
--------- ----------- ----------- 
1         74          10
2         60          0

(2 row(s) affected)

*/

DROP PROCEDURE dbo.sp_CrossTab;
DROP TABLE dbo.Table1;

-- Step 4: End of SQL String
SET @sql = @sql +' INTO NuovaTabella '        + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

>piano piano arrivo al resto e mica sono Lorenzo Benaglia?

-- Step 4: End of SQL String
SET @sql = @sql +' INTO NuovaTabella '        + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

SET @sql = @sql +' INTO '+ @newTABLE +' '    +
  'FROM ' + @table        +' '+
  'GROUP BY ' + @onrows   +' '+
  'ORDER BY ' + @onrows

+ CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol

-- Step 3: Middle Part of SQL String
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN '  @sumcol + @NEWLINE +
    '        ELSE 0'                      + @NEWLINE +
    '      END) AS [' + @key + ']'
  
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END