Recentemente, ho avuto l’occasione di realizzare un’estrazione dati in cui era richiesto di dividere le righe di una colonna di tipo VARCHAR per ottenere due colonne ordinate tra loro, una specie di partizione dinamica ordinata.
Scenario
Disponiamo di un database SQL Server in cui sono presenti le tabelle anagrafiche dbo.Magazzini e dbo.Vani utilizzate rispettivamente per memorizzare l’anagrafica dei magazzini aziendali ed la relativa suddivisione in vani.
Le tabelle dbo.Magazzini e dbo.Vani hanno la seguente struttura:
dbo.Magazzini:
— Drop table dbo.Magazzini if exists
IF (OBJECT_ID(‘Magazzini’, ‘U’) IS NOT NULL)
DROP TABLE dbo.Magazzini
GO
— Create table dbo.Magazzini
CREATE TABLE [dbo].[Magazzini]
(
ID INT IDENTITY(1, 1) NOT NULL,
CODICE CHAR(4) NOT NULL,
DESCRIZIONE CHAR(80) NOT NULL
PRIMARY KEY (ID)
)
dbo.Vani:
— Drop table dbo.Vani if exists
IF (OBJECT_ID(‘Vani’, ‘U’) IS NOT NULL)
DROP TABLE dbo.Vani
GO
— Create table dbo.Vani
CREATE TABLE [dbo].[Vani]
(ID INT IDENTITY(1, 1) NOT NULL,
CODICE CHAR(6) NOT NULL,
DESCRIZIONE CHAR(80) NOT NULL,
IDMAGAZZINI INT NOT NULL
PRIMARY KEY (ID)
)
— Foreign Key
ALTER TABLE [dbo].[Vani] WITH NOCHECK ADD CONSTRAINT [FK__Magazzini_ID] FOREIGN KEY (IDMAGAZZINI) REFERENCES [dbo].[Magazzini] (ID)
— Inserimento dati di prova
INSERT INTO [dbo].[Magazzini] (CODICE, DESCRIZIONE) VALUES (‘MATP’, ‘Materie Prime’)
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select ‘VTERRA’, ‘Vano a Terra’, (Select Id From Magazzini Where Codice=’MATP’)
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select ‘CAS001’, ‘Cassetto 001′, (Select Id From Magazzini Where Codice=’MATP’)
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select ‘CAS002’, ‘Cassetto 002′, (Select Id From Magazzini Where Codice=’MATP’)
INSERT INTO [dbo].[Magazzini] (CODICE, DESCRIZIONE) VALUES (‘SMLA’, ‘Semilavorati’)
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select ‘VTERRA’, ‘Vano a Terra’, (Select Id From Magazzini Where Codice=’SMLA’)
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select ‘CAS003’, ‘Cassetto 003′, (Select Id From Magazzini Where Codice=’SMLA’)
Le tabelle anagrafiche dbo.Magazzini e dbo.Vani contengono rispettivamente i seguenti dati di prova:
L’output richiesto prevede la divisione della colonna dbo.Vani.Codice in due colonne ordinate alfabeticamente tra loro da sinistra a destra.
Soluzione
La stored procedure dbo.USP_SPLIT_COLUMN, definita di seguito, permette di ottenere l’output richiesto.
— Drop procedure
IF (OBJECT_ID(‘USP_SPLIT_COLUMN’) IS NOT NULL)
DROP PROCEDURE dbo.USP_SPLIT_COLUMN
GO
— Create procedure
CREATE PROCEDURE dbo.USP_SPLIT_COLUMN
(@TableName AS SYSNAME,
@AliasName AS VARCHAR(20),
@FieldPK AS SYSNAME,
@FieldName AS VARCHAR(20),
@FieldName1 AS VARCHAR(20),
@WhereCond AS VARCHAR(1024),
@TableJoin AS SYSNAME = ”,
@AliasJoin AS VARCHAR(20) = ”,
@FieldJoinLeft AS VARCHAR(20) = ”,
@FieldJoinRight AS VARCHAR(20) = ”,
@FieldJoinList AS VARCHAR(1024) = ”)
AS
BEGIN
/*
Descrizione:
Divisione di una colonna di tipo VarChar.
Output ordinato su due colonne.
Non vengono l’utilizzo di cursori.
Parametri:
(*) @TableName AS SYSNAME : Tabella principale (FROM)
(*) @AliasName AS VARCHAR(20) : Alias tabella principale
(*) @FieldPK AS SYSNAME : PK (ID) tabella principale
(*) @FieldName AS VARCHAR(20) : Campo su cui eseguire lo Split
(*) @FieldName1 AS VARCHAR(20) : Campo descrittivo tabella principale
(*) @WhereCond AS VARCHAR(1024) : Criterio di filtro tabella principale
@TableJoin AS SYSNAME : Tabella secondaria da citare nella LEFT OUTER JOIN
@AliasJoin AS VARCHAR(20) : Alias tabella secondaria da citare nella LEFT OUTER JOIN
@FieldJoinLeft AS VARCHAR(20) : Campo citato nella LEFT OUTER JOIN relativo a @TableJoin
@FieldJoinRight AS VARCHAR(20) : Campo citato nella LEFT OUTER JOIN relativo alla tabella principale
@FieldJoinList AS VARCHAR(1024) : Campi estratti da @TableJoin
(*) = Parametro obbligatorio
Ultimo aggiornamento: 17/09/2008
*/
DECLARE @CMD AS VARCHAR(1024)
SET @CMD = ”
CREATE TABLE #tmpRENUM
(ID INT IDENTITY(1, 1) NOT NULL,
IDTABLE INT NOT NULL)
SET @CMD = ‘INSERT INTO #tmpRENUM (IDTABLE) ‘ +
‘SELECT ‘+LTRIM(RTRIM(@AliasName)) + ‘.’ + LTRIM(RTRIM(@FieldPK)) + ‘ ‘ +
‘FROM ‘ + LTRIM(RTRIM(@TableName)) + ‘ ‘ + LTRIM(RTRIM(@AliasName)) + ‘ ‘ +
LTRIM(RTRIM(@WhereCond))+ ‘ ‘ +
‘ORDER BY ‘ + LTRIM(RTRIM(@AliasName)) + ‘.’ + LTRIM(RTRIM(@FieldName))
EXEC(@CMD)
PRINT @CMD
SET @CMD = ‘SELECT ‘ +
‘A1.’+LTRIM(RTRIM(@FieldName))+’ AS ‘ + LTRIM(RTRIM(@FieldName))+’1, ‘ +
‘A1.’+LTRIM(RTRIM(@FieldName1))+’ AS ‘ + LTRIM(RTRIM(@FieldName1))+’1, ‘
IF (LTRIM(RTRIM(@TableJoin)) <> ”)
SET @CMD = @CMD +
LTRIM(RTRIM(@FieldJoinList))+ ‘ AS FieldInfo1, ‘
SET @CMD = @CMD +
‘( SELECT A2.’+LTRIM(RTRIM(@FieldName))+ ‘ ‘ +
‘FROM #tmpRENUM AS C2 ‘ +
‘INNER JOIN ‘+LTRIM(RTRIM(@TableName))+’ A2 ON C2.idtable=A2.’+LTRIM(RTRIM(@FieldPK))+’ ‘+
‘WHERE (C2.id = (C1.id + 1)) ‘ +
‘) as ‘+LTRIM(RTRIM(@FieldName))+’2, ‘ +
‘( SELECT A2.’+LTRIM(RTRIM(@FieldName1))+ ‘ ‘ +
‘FROM #tmpRENUM AS C2 ‘ +
‘INNER JOIN ‘+LTRIM(RTRIM(@TableName))+’ A2 ON C2.idtable=A2.’+LTRIM(RTRIM(@FieldPK))+’ ‘+
‘WHERE (C2.id = (C1.id + 1)) ‘ +
‘) as ‘+LTRIM(RTRIM(@FieldName1))+’2 ‘
IF (LTRIM(RTRIM(@TableJoin)) <> ”)
SET @CMD = @CMD +
‘, ( SELECT ‘+LTRIM(RTRIM(@FieldJoinList))+ ‘ ‘ +
‘FROM #tmpRENUM AS C2 ‘ +
‘INNER JOIN ‘+LTRIM(RTRIM(@TableName))+’ A2 ON C2.idtable=A2.’+LTRIM(RTRIM(@FieldPK))+’ ‘+
‘LEFT OUTER JOIN ‘+LTRIM(RTRIM(@TableJoin))+’ ‘+LTRIM(RTRIM(@AliasJoin))+ ‘ ON ‘+LTRIM(RTRIM(@AliasJoin))+’.’+LTRIM(RTRIM(@FieldJoinLeft))+’ = A2.’+LTRIM(RTRIM(@FieldJoinRight)) + ‘ ‘ +
‘WHERE (C2.id = (C1.id + 1)) ‘ +
‘) as FieldInfo2 ‘
SET @CMD = @CMD +
‘FROM #tmpRENUM AS C1 ‘ +
‘INNER JOIN ‘+LTRIM(RTRIM(@TableName))+’ A1 ON C1.idtable=A1.’+LTRIM(RTRIM(@FieldPK))+’ ‘
IF (LTRIM(RTRIM(@TableJoin)) <> ”)
SET @CMD = @CMD +
‘LEFT OUTER JOIN ‘+LTRIM(RTRIM(@TableJoin))+’ ‘+LTRIM(RTRIM(@AliasJoin))+ ‘ ON ‘+LTRIM(RTRIM(@AliasJoin))+’.’+LTRIM(RTRIM(@FieldJoinLeft))+’ = A1.’+LTRIM(RTRIM(@FieldJoinRight)) + ‘ ‘
SET @CMD = @CMD +
‘WHERE (C1.id % 2) <> 0 ‘ +
‘ORDER BY ‘+LTRIM(RTRIM(@FieldName))+’1’
EXEC(@CMD)
PRINT @CMD
END
Esempio
Il comando seguente invoca la stored procedure dbo.USP_SPLIT_COLUMN per dividere in due (split) la colonna codice della tabella dbo.Vani: