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