Home > Scripts > Split ordinato di una colonna di tipo VarChar

Split ordinato di una colonna di tipo VarChar

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:

 

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:

 

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:

 

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:

 

EXEC USP_SPLIT_COLUMN ‘Vani’, ‘V’, ‘ID’, ‘Codice’, ‘Descrizione’, ”, ”, ”, ”, ”, ”

 

 

Split: output

 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Aggiornamento statistiche SQL per database che contengono External Table PolyBase

Recentemente, ho avuto l’occasione di configurare un piano di manutenzione database per un DB in …