salve,
una "rotazione" di questo tipo puo' essere fatta in svariati modi, ad esempio tramite una funzione utente che "aggreghi" i vari "colori" filtrando per il parametro passato come filtro, quindi qualche cosa simile
DECLARE @Codice char(3) = '005'; -- parametro passato alla funzione
DECLARE @value varchar(100) = '';
SELECT @value += ', ' + t1.Valore
FROM dbo.t1
WHERE t1.Codice = @Codice
ORDER BY t1.Valore;
SELECT STUFF(@value, 1, 2, '');
dove una variabile interna alla funzione utente funge da "accumulatore" permettendo, per ogni iterazione di riga, di aggiungere il valore della colonna alla variabile indicata....
ma oltre a essere molto poco performante, questo metodo ha anche dei "problemi" legati al potenziale parallelismo dell'operazione che puo' risultare in ordinamenti non corretti nella restituzione del "risultato" della funzione...
c'e' il metodo "simile" al precedente basato su cursori, che "ovviamente" non andiamo neanche a introdurre/esemplificare in quanto l'uso di cursori e' deprecabile se non strettamente necessario :)
con l'introduzione di SQL Server 2005 si puo' anche usare una funzione CLR che esegua l'aggregazione, e le sue performance non sono in effetti deprecabili...
ma sempre da SQL Server 2005 e' stata potenziata l'istruzione FOR XML che consente invece sempre un ordinamento corretto nel risultato desiderato, e puo' essere addirittura innestato, ed ovviamente cerchiamo di preferire soluzioni basate su SQL :)
in questo senso possiamo usare un semplice metodo di innesto dell'aggregazione in una subquery, similarmente a
SELECT DISTINCT t1.Codice,
SUBSTRING(
(SELECT ', ' + Valore AS [text()]
FROM dbo.t1 st1
WHERE st1.Codice = t1.Codice
ORDER BY st1.Codice
FOR XML PATH ('')
)
, 3, 1000) [Colori]
FROM dbo.t1 t1
ORDER BY t1.Codice;
come anche usare piu' fantasia applicando un'altra novita' di SQL Server 2005 basata sull'operatore CROSS APPLY, similarmente a
SELECT DISTINCT Codice, Valori
FROM dbo.t1 t1
CROSS APPLY ( SELECT Valore + ','
FROM dbo.t1 st1
WHERE st1.Codice = t1.Codice
ORDER BY Codice
FOR XML PATH('') ) D ( Valori )
come anche "circonvoluzioni ricorsive" basate su un bellissimo ed elegantissimo esempio proposto da Vadim Tropashko, un genio riconosciuto nell'ambiente dei database relazionali, similarmente a
WITH CTE ( Codice, Colori, Nome_Valore, Lunghezza ) AS (
SELECT Codice
, CAST( '' AS VARCHAR(8000) )
, CAST( '' AS VARCHAR(8000) )
, 0
FROM dbo.t1
GROUP BY Codice
UNION ALL
SELECT p.Codice
, CAST( Colori +
CASE WHEN Lunghezza = 0 THEN '' ELSE ', ' END + Valore AS VARCHAR(8000) )
, CAST( Valore AS VARCHAR(8000))
, Lunghezza + 1
FROM CTE c
INNER JOIN dbo.t1 p
ON c.Codice = p.Codice
WHERE p.Valore > c.Nome_Valore
)
SELECT Codice
, Colori
FROM (
SELECT Codice
, Colori
, RANK() OVER ( PARTITION BY Codice ORDER BY Lunghezza DESC )
FROM CTE ) D ( Codice, Colori, rank )
WHERE rank = 1;
ma probabilmente stiamo veramente giocando per divertirci...
personalmente userei il semplice metodo della subquery...
a titolo di completezza, comunque, il triviale esempio completo..
saluti
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t1 (
Codice char(3) NOT NULL,
Valore varchar(10) NOT NULL
);
INSERT INTO dbo.t1
VALUES ('001', 'Blu'), ('001', 'Rosso');
INSERT INTO dbo.t1
VALUES ('002', 'Giallo'), ('002', 'Blu');
INSERT INTO dbo.t1
VALUES ('003', 'Nero');
INSERT INTO dbo.t1
VALUES ('004', 'Blu'), ('004', 'Rosso'), ('004', 'Giallo');
INSERT INTO dbo.t1
VALUES ('005', 'Blu'), ('005', 'Rosso'), ('005', 'Giallo'), ('005', 'Nero');
GO
-- metodo piu' semplice
SELECT DISTINCT t1.Codice,
SUBSTRING(
(SELECT ', ' + Valore AS [text()]
FROM dbo.t1 st1
WHERE st1.Codice = t1.Codice
ORDER BY st1.Codice
FOR XML PATH ('')
)
, 3, 1000) [Colori]
FROM dbo.t1 t1
ORDER BY t1.Codice;
-- metodo basato su una funzione utente qui da generare con il
-- costrutto CREATE FUNCTION dbo.ufn_get_colori_from_codice
DECLARE @Codice char(3) = '005';
DECLARE @value varchar(100) = '';
SELECT @value += ', ' + t1.Valore
FROM dbo.t1
WHERE t1.Codice = @Codice
ORDER BY t1.Valore;
SELECT STUFF(@value, 1, 2, '');
-- metodo con CROSS APPLY
SELECT DISTINCT Codice, Valori
FROM dbo.t1 t1
CROSS APPLY ( SELECT Valore + ','
FROM dbo.t1 st1
WHERE st1.Codice = t1.Codice
ORDER BY Codice
FOR XML PATH('') ) D ( Valori );
-- metodo fantasioso di Tropashko
WITH CTE ( Codice, Colori, Nome_Valore, Lunghezza ) AS (
SELECT Codice
, CAST( '' AS VARCHAR(8000) )
, CAST( '' AS VARCHAR(8000) )
, 0
FROM dbo.t1
GROUP BY Codice
UNION ALL
SELECT p.Codice
, CAST( Colori +
CASE WHEN Lunghezza = 0 THEN '' ELSE ', ' END + Valore AS VARCHAR(8000) )
, CAST( Valore AS VARCHAR(8000))
, Lunghezza + 1
FROM CTE c
INNER JOIN dbo.t1 p
ON c.Codice = p.Codice
WHERE p.Valore > c.Nome_Valore
)
SELECT Codice
, Colori
FROM (
SELECT Codice
, Colori
, RANK() OVER ( PARTITION BY Codice ORDER BY Lunghezza DESC )
FROM CTE ) D ( Codice, Colori, rank )
WHERE rank = 1;
GO
DROP TABLE dbo.t1;
ovviamente dai anche un'occhiata ai piani di esecuzione generati...
saluti
Modificato da Andrea Montanari il 01 aprile 2015 19.32 -
