SQL Server ed ALM su database


Il blog di Alessandro Alpi
Archivio Posts
Anno 2018

Anno 2017

Anno 2016

Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Anno 2006

Replicare righe in base a valore di colonna

Oggi è arrivata una richiesta sul forum, replicare i record in base al numero contenuto in una colonna. Diciamo una trasformazione: da una tabella con una riga per ogni chiave, ad una che contiene tante righe per chiave quanto un valore specifica.
Devo dire che non è la prima volta che mi trovo domande di questo tipo, quindi ho deciso di scirivere qui una possibile soluzione.

Partiamo con la tabella dei "parametri":

CREATE TABLE #ParameterTable

(

    ID int NOT NULL PRIMARY KEY CLUSTERED

  , Value int NOT NULL

  , RepeatValue int NOT NULL 

);

GO

 

INSERT INTO #ParameterTable (ID, Value, RepeatValue)

VALUES

    (1, 2, 4)

  , (2, 9, 3)

  , (3, 7, 20);

GO


Come vedete è una tabella che contiene, per ogni id il numero di volte in cui ripetere la riga in output.
Ciò significa che per il primo record (con ID = 1) devo ottenere in output 4 righe, per il secondo 3, e per il terzo 20.

Il tutto è stato risolto con una CTE (Common Table Expression), in questo modo:

WITH ReplicateTable(ID, Value, RepeatValue, RepeatedTime) as

        (

      SELECT

          ID

        , Value

        , RepeatValue

        , 1

      FROM

        #ParameterTable

      UNION ALL

      SELECT

          ID

        , Value

        , RepeatValue

        , RepeatedTime + 1

      FROM

        ReplicateTable R

      WHERE

        R.RepeatedTime < R.RepeatValue

        )

SELECT

    ID

  , Value

  , RepeatedTime

FROM

    ReplicateTable

ORDER BY

    ID

  , RepeatedTime;

GO



La CTE ci consente di effettuare una ricorsione e quindi di utilizzare il filtro indicato nella where del secondo statement, dopo la UNION, per iterare. Il risultato è il seguente:

ID          Value      RepeatedTime

----------- ---------- ------------

1           2          1

1           2          2

1           2          3

1           2          4

2           9          1

2           9          2

2           9          3

3           7          1

3           7          2

3           7          3

3           7          4

3           7          5

3           7          6

3           7          7

3           7          8

3           7          9

3           7          10

3           7          11

3           7          12

3           7          13

3           7          14

3           7          15

3           7          16

3           7          17

3           7          18

3           7          19

3           7          20

27 righe, quelle che ci aspettavamo (4 + 3 + 20).

Nel caso in cui il RepeatValue vale 0, ottenitamo però, con questa query, un risultato errato. Impostando 0 come valore di ripetizione nel terzo record (ID = 3) otteniamo quanto segue:

ID          Value      RepeatedTime

----------- ---------- ------------

1           2          1

1           2          2

1           2          3

1           2          4

2           9          1

2           9          2

2           9          3

3           7          1 --> il record appare e con RepeatedTime errato



Per migliorare, è sufficiente cambiare il membro di start, ovvero la condizione della prima select nella CTE:

WITH ReplicateTable(ID, Value, RepeatValue, RepeatedTime) as

        (

      SELECT

          ID

        , Value

        , RepeatValue

        , 1

      FROM

        #ParameterTable

      WHERE

        RepeatValue <> 0

      UNION ALL

      SELECT

          ID

        , Value

        , RepeatValue

        , RepeatedTime + 1

      FROM

        ReplicateTable R

      WHERE

        R.RepeatedTime < R.RepeatValue

        )


Con questo filtro, escludiamo già di partenza la ricorsione su tutti i record il cui RepeatValue vale zero.
Tutto sta nel capire cosa significa quel valore di ripetizione. Il richiedente potrebbe voler far comparire il record una sola volta se NON E' RIPETUTO. In tal caso quindi, lo zero assume tutt'altro significato. 
Ciò significa che la richiesta diventa: "si vuole mostrare ogni record almeno una volta (caso di ripetizione 0) più tutte le volte che deve essere ripetuto". 
La CTE diventa la seguente:

WITH ReplicateTable(ID, Value, RepeatValue, RepeatedTime) as

        (

SELECT

          ID

        , Value

              , RepeatValue

        , 0

      FROM

        #ParameterTable

      UNION ALL

      SELECT

          ID

        , Value

              , RepeatValue

        , RepeatedTime + 1

      FROM

        ReplicateTable R

      WHERE

        R.RepeatedTime < R.RepeatValue

)


Stavolta abbiamo agito sul RepeatedTime. E il risultato che avremo è il seguente:

ID          Value      RepeatedTime

----------- ---------- ------------

1           2          0

1           2          1

1           2          2

1           2          3

1           2          4

2           9          0

2           9          1

2           9          2

2           9          3

3           7          0

I risultati sottolineati sono quelli NON RIPETUTI, ma che appaiono comunque la prima volta.

Dipende molto dal tipo di richiesta, ma con le CTE ci si può sbizzarrire abbastanza, anche se c'è da fare attenzione, come sempre, alle performance e ai livelli di ricorsione che si vogliono raggiungere.
Se volete migliorare ancora lo script, potete creare una stored procedure parametrizzata che si adatti a quanto vi viene commissionato. Mi raccomando, controllate i piani di esecuzione generati.

Stay tuned! 



Categoria: Transact-SQL
giovedì, 04 lug 2013 Ore. 10.55
Statistiche
  • Views Home Page: 482.864
  • Views Posts: 870.801
  • Views Gallerie: 500.649
  • n° Posts: 484
  • n° Commenti: 273



















Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003