Creare una tabella dinamicamente!

lunedì 18 settembre 2006 - 20.58

bluland Profilo | Guru

Salve,

vorrei realizzare un SP, con SQL Server 2000 che faccia la seguente cosa:

ho inizialmente la seguente tabella :

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)

vorrei creare una SP, che una volta lanciata mi prenda questa tabella e mi crei una specie di trasposta di questo tipo:

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

in pratica nella colonna matricola vengono riportate le matricole univocamente da table1,
poi le colonne vengono create a secondo dei record che ci sono in table1.anno_week con
SUBSTRING(table1.anno_week,1,4), creando quindi una colonna per ogni anno, ed infine quest'ultime saranno riempire con la sommatoria di tutte le occorrenze per quell'anno e per quella matricola, come da esempio il primo record: ha 74 per il 2006 cioè
200601 +(42) 200633 +(22) 200629 +(10) = 74 per la matricola 1



spero di essere stato chiaro....
Vorrei dei consigli su come procedere, idee?

saluti

ENzo

lbenaglia Profilo | Guru

>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)
>
>
>vorrei creare una SP, che una volta lanciata mi prenda questa
>tabella e mi crei una specie di trasposta di questo tipo:
>
>Results
>------------------
>matricola 2006 2007
> --------- --------- --------
> 1 74 10
> 2 60 0
>
>...ed infine quest'ultime saranno riempire con la sommatoria
>di tutte le occorrenze per quell'anno e per quella matricola,
>come da esempio il primo record: ha 74 per il 2006 cioè
>200601 +(42) 200633 +(22) 200629 +(10)
>= 74 per la matricola 1

Ti sei perso un 60 per strada oppure non ho capito io?

Edit 21:37
Ho capito male io
Non avevo visto che la seconda riga è riferita alla Matricola 2.

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

lbenaglia Profilo | Guru

>spero di essere stato chiaro....
>Vorrei dei consigli su come procedere, idee?

Allora, SQL Server 2000 non offre nativamente la possibilità di creare query a campi incrociati.
Ad ogni modo sul numero di Novembre 2000 di SQL Server Magazine, Itzik Ben-Gan ha affrontato questo problema, proponendo una Stored Procedure in grado di generare query crosstab dinamiche.
L'articolo è disponibile al seguentre link (solo agli abbonati alla rivista):
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

Proviamo ad applicare la sp_CrossTab al tuo esempio:

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;

Studia bene il funzionamento della stored procedure, è complessa solo in apparenza.
Scommenta la linea PRINT @sql + @NEWLINE e commenta la linea successiva EXEC (@sql) in modo da visualizzare il codice SQL senza eseguirlo.

SQL Server 2005 implementa i nuovi operatori PIVOT e UNPIVOT per definire query a campi incrociati ma putroppo sono statici, ovvero occorre conoscere a priori i valori che assumeranno le colonne.
Un buon esercizio potrebbe essere quello di aggiornare la sp_CrossTab utilizzando l'operatore PIVOT... perché non ci provi postando la soluzione?

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

>Allora, SQL Server 2000 non offre nativamente la possibilità
>di creare query a campi incrociati.
>Ad ogni modo sul numero di Novembre 2000 di SQL Server Magazine,
>Itzik Ben-Gan ha affrontato questo problema, proponendo una Stored
>Procedure in grado di generare query crosstab dinamiche.
>L'articolo è disponibile al seguentre link (solo agli abbonati
>alla rivista):
>http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608
>
>Proviamo ad applicare la sp_CrossTab al tuo esempio:
>
>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;
>
>Studia bene il funzionamento della stored procedure, è complessa
>solo in apparenza.
>Scommenta la linea PRINT @sql + @NEWLINE e commenta la linea
>successiva EXEC (@sql) in modo da visualizzare il codice SQL
>senza eseguirlo.

A prima visto vorrei chiederti una cosa, ma la tabella che si crea non è persistente?? ti chiedo questo poiche non vedo nessun create table!!

>
>SQL Server 2005 implementa i nuovi operatori PIVOT e UNPIVOT
>per definire query a campi incrociati ma putroppo sono statici,
>ovvero occorre conoscere a priori i valori che assumeranno le
>colonne.

si ricordo mi facesti un esempio l'altra volta che chiesi la trasposta, che poi non è una trasposta ma si chiama cross table

>Un buon esercizio potrebbe essere quello di aggiornare la sp_CrossTab
>utilizzando l'operatore PIVOT... perché non ci provi postando
>la soluzione?

beh un buon esercizio è gia capire questa come funziona! poi piano piano arrivo al resto e mica sono Lorenzo Benaglia?
>
>Ciao!
Ciao
>
>--
>Lorenzo Benaglia
>Microsoft MVP - SQL Server
>http://blogs.dotnethell.it/lorenzo/
>http://italy.mvps.org

lbenaglia Profilo | Guru

>A prima visto vorrei chiederti una cosa, ma la tabella che si
>crea non è persistente?? ti chiedo questo poiche non vedo nessun
>create table!!
E' un result set. Se vuoi crearti una tabella dovresti modificare lo step 4 nel seguente modo:

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

Ovviamente il nome della tabella lo puoi parametrizzare...

>si ricordo mi facesti un esempio l'altra volta che chiesi la
>trasposta, che poi non è una trasposta ma si chiama cross table
In dialetto Brianzolo ti direi "Lè istess"

>beh un buon esercizio è gia capire questa come funziona! poi
>piano piano arrivo al resto e mica sono Lorenzo Benaglia?
E meno male, al mondo ne basta già uno a fare tutti quei danni

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

grazie ancora vado a studiare!!

mi sa che mi devo abbonare anche io a sqlmagazine!!

Ciao

Enzo

bluland Profilo | Guru

Ciao ho studiato la SP, e vorrei farti una domanda a proposito della variabile @newline, prendo da esempio questo pezzo di codice:

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


beh se l'avessi scritto cosi non sarebbe la stessa cosa?

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

perche' usare una nuova variabile??

saluti

lbenaglia Profilo | Guru

>Ciao ho studiato la SP, e vorrei farti una domanda a proposito
>della variabile @newline
La variabile @NEWLINE è definita come SET @NEWLINE = CHAR(10), ovvero il carattere ASCII "line feed" che sulle stampanti fa avanzare la testina di stampa sulla riga successiva.
Questo carattere serve solo per fare tornare a capo la riga, formattando in modo leggibile il comando SQL.
Al fine del comando non ha alcun effetto, ma ti suggerisco di scrivere in modo ESTREMAMENTE ordinato i tuoi comandi SQL in modo da renderne semplice la lettura e l'eventuale modifica.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

ok grazie

bluland Profilo | Guru

ciao sempre io!

step 4.
ma perche usa WHEN N''' la N a cosa serve?

saluti

Enzo

lbenaglia Profilo | Guru

>ma perche usa WHEN N''' la N a cosa serve?
Parolina magica: UNICODE
Ora fai una buona ricerca sui Books Online...

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

a questo punto non ho capito quando va usato, o meglio nella documentazione on line dice quando l'applicazione e' Unicode-enabled bisogna usare unicode, beh ma allora nel mio caso specifico potrei anche omettere N?

saluti

lbenaglia Profilo | Guru

>a questo punto non ho capito quando va usato, o meglio nella
>documentazione on line dice quando l'applicazione e' Unicode-enabled
>bisogna usare unicode, beh ma allora nel mio caso specifico potrei
>anche omettere N?
Sigh... se una colonna, variabile, parametro è definita come UNICODE, allora andrebbe utilizzata la "N" prima di un literal se non si vuole che avvenga un cast implicito.

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

hello ho una domanda su questa parte di codice:


step3.

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

perche nel caso sumcol di NULL porlo = 1?


saluti

EDIT: HO provato a togliere questo case facendo in questo modo:

-- 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


e' mi da questo Warning:

Warning: Null value is eliminated by an aggregate or other SET operation.
dovrei quindi evitare di mettere valori NULL nella tabella da trasporre, server per questo tipo di controllo l'istruzione che non ho capito bene?

risaluti

lbenaglia Profilo | Guru

>hello ho una domanda su questa parte di codice:
>
>
>step3.
>
>+ CASE
> WHEN @sumcol IS NULL THEN '1'
> ELSE @sumcol
>
>perche nel caso sumcol di NULL porlo = 1?

Perché se non si passa alla stored procedure una colonna di cui si vuole la sommatoria, verrà semplicemente restituito il numero di righe raggruppate.
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

ciao,

volevo chiederti ha qualche idea su come superare il limite dei 8000 caratteri della variabile sql
(@sql AS varchar(8000),), infatti tale problema e' sorto quando ho cercato di trasporre una tabella molto grande.

saluti

ENzo

lbenaglia Profilo | Guru

>volevo chiederti ha qualche idea su come superare il limite dei
>8000 caratteri della variabile sql
>(@sql AS varchar(8000),), infatti tale problema e' sorto quando
>ho cercato di trasporre una tabella molto grande.

Non ho soluzioni, se non passare a 2005

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

perche' ha qualche tipo di variabile diversa_? o cambi proprio la metodologia?

lbenaglia Profilo | Guru

>perche' ha qualche tipo di variabile diversa_? o cambi proprio
>la metodologia?
Entrambe le cose
SQL Server 2005 offre i nuovi operatori PIVOT e UNPIVOT per creare query crosstab (anche se purtroppo sono ancora statiche) e "manda in pensione" i tipi blob utilizzati fino ad ora (image, text, ntext) sostituendoli rispettivamente con varbinary(max), varchar(max), nvarchar(max).

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

mmm avevo pensato di dichiarare tipo 1000 variabili sql ed in ognuno mettere un pezzo di query, ma sarebbe dispendiosissimo, la butto là magari dico una "biiiip",
e se invece di salvarla in una variabile varchar la query venisse salvata in un file e poi letta dal file stesso al momento dell'esecuzione??

è solo un idea, ma non saprei dove mettere le mani!!

Ciao!

lbenaglia Profilo | Guru

>mmm avevo pensato di dichiarare tipo 1000 variabili sql ed in
>ognuno mettere un pezzo di query, ma sarebbe dispendiosissimo,
>la butto là magari dico una "biiiip",

Ma che senso avrebbe traslare una query del genere?
Oltre ad essere dispendiosissima non ne vedo l'utilità.

>e se invece di salvarla in una variabile varchar la query venisse
>salvata in un file e poi letta dal file stesso al momento dell'esecuzione??
>
>è solo un idea, ma non saprei dove mettere le mani!!
Semplice, in SQL Server 2000 non puoi leggere un file utilizzando i comandi T-SQL, mentre con SQL Server 2005 la cosa è possibile utilizzando la funzione OPENROWSET ed il bulk rowset provider.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

bluland Profilo | Guru

quindi poiche non posso usare il 2005 a questo punto qualsiasi soluzione(multiple variabili, cursori e chi + ne + ne metta) risulterebbe dispendiosa!!

mi arrendo!

bluland Profilo | Guru

Buongiorno,

la ributto la',
stanotte mentre dormivo ho pensato a quest'alta soluzione, invece di mettere tutto il una variabile varchar
ho pensato di mettere tutto la uqery finale in una tabella con un campo IMAGE e poi in qualche modo eseguire la query leggendo da questo campo!

che ne pensi?


CIao!
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2025
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5