Salve,
allora, “tutto dinamico”, quindi la proiezione in ordine del numero di colonne dipende dal MAX(';') prensente nelle righe che verranno proiettate in base ad esempio al filtro su Campo1…
se verranno ritornate 2 righe, con Campo2 = ‘n1;n2;n3’ e ‘n1;n2’ saranno 3 colonne, e cosi' via…
quindi dovremo purtroppo costruire un comando da eseguire dinamicamente con ad esempo sp_executesql… e dobbiamo prima sapere QUANTE colonne ruotare…
SET NOCOUNT ON;
GO
USE tempdb;
GO
CREATE TABLE Test (Campo1 int, Campo2 varchar(100));
GO
INSERT INTO Test
VALUES ( 1, '12345;' ), ( 11, '' ), ( 111, '123456' ),
( 2, '1111111;2' ), ( 22, '1111;2222;' ),
( 3, '311111;32222222;333333333333'), ( 33, '3111000000022;33333333;3'),
( 100, '1;2;3;4;5;6;7;8;9;10');
GO
DECLARE @maxCol int = 1;
DECLARE @maxId int = 20;
SELECT @maxCol = MAX(c)
FROM (
SELECT COUNT(ap.value) c
FROM Test t
CROSS APPLY (
SELECT value
FROM STRING_SPLIT(Campo2, ';')
WHERE LEN(ISNULL(value, '')) > 0
) ap
WHERE Campo1 <= @maxId
GROUP BY Campo1, Campo2) x;
DECLARE @cmd nvarchar(4000) ='';
DECLARE @loop int = 1;
WHILE @loop <= @maxCol BEGIN
SET @cmd = @cmd + CASE WHEN LEN(@cmd) > 0 THEN ', ' ELSE '' END + 'MAX(CASE WHEN rn = ' + CONVERT(varchar, @loop) + ' THEN Value ELSE NULL END) AS ' + QUOTENAME('Col' + CONVERT(varchar, @loop))
SET @loop += 1;
END;
SET @cmd ='WITH cte AS (
SELECT t.*
, ROW_NUMBER() OVER (PARTITION BY Campo1 ORDER BY Campo1) AS rn
, CASE WHEN LEN(ISNULL(Value, '''')) = 0 THEN NULL ELSE Value END AS Value
FROM Test t
CROSS APPLY (
SELECT value
FROM STRING_SPLIT(Campo2, '';'')
WHERE LEN(ISNULL(value, '''')) > 0
) ap
WHERE Campo1 <= ' + CONVERT(varchar, @maxId) + '
)
SELECT t.Campo1
, ' + @cmd + '
FROM cte t
GROUP BY t.Campo1
ORDER BY Campo1;';
EXEC sp_executesql @cmd;
GO
DROP TABLE Test;
--<--------
Campo1 Col1 Col2 Col3
----------- ----------- ----------- ---------------
1 12345 NULL NULL
2 1111111 2 NULL
3 311111 32222222 333333333333
11 NULL NULL NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
se per @maxId usassimo 100 o 111, avremmo 10 colonne generate.
In questo caso, per semplicita' di pensiero, ho effettuato la rotazione con soppressione di riga tramite il aggregazione MAX(..) e GROUP BY, che risulta nell ANSI warning che ho voluto appositamente riportare… si potrebbe gestire anche in altri modi…
comunque non e' una bellissima cosa :D e' sicuramente pesante anche per la ripetizione di esecuzione, dove la prima esecuzione e' deputata a ottenere il solo numero di colonne da proiettare…
Non sarei felice di usare una soluzione di questo tipo :D
salutoni romagnoli
–
Andrea