384 messaggi dal 26 agosto 2007
Ciao a tutti,
oggi sono incappato in questo problema apparentemente banale ma non riesco proprio a venirne fuori. Ho questa semplice tabella

TABELLA:
Codice | Valore
----------------------
001 | Rosso
001 | Blu
002 | Giallo
002 | Blu

ecc.....

Vorrei avere come risultato i seguenti record:

Codice | Valore
----------------------
001 | Rosso,Blu
002 | Giallo,Blu

Come posso fare?
Grazie!!!

Lunga vita e prosperità!!
1.953 messaggi dal 27 luglio 2005
Contributi
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 -

Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php
384 messaggi dal 26 agosto 2007
Non so come ringraziarti!! Ho adottato questa soluzione qui

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;



Approfitto per chiederti una delucidazione. La funzione text() che hai usato "SELECT ', ' + Valore AS [text()]" serve per fare una sorta di cast del risultato del campo sul tipo text? Se è così, sarebbe stato lo stesso scrivere "SELECT CAST(', ' + Valore AS text)"?

Un'altra cosa, la funzione FOR XML PATH fa automaticamente un'iterazione sul campo per concatenare i risultati?

Lunga vita e prosperità!!
1.953 messaggi dal 27 luglio 2005
Contributi
salve,
>La funzione text() che hai usato "SELECT ', ' + Valore AS [text()]" serve per fare una sorta di cast del risultato del campo sul tipo text? Se è così, sarebbe stato lo stesso scrivere "SELECT CAST(', ' + Valore AS text)"?

attenzione, AS [text()] non e' una chiamata a funzione o cast a qualche cosa, e' solo l'indicazione nominativa della colonna restituita... non e' neanche sempre necessaria e, a dire il vero, e' una semplice mia consuetudini quando "lavoro" con risultati XML
puoi anche tranquillamente scrivere
SELECT DISTINCT t1.Codice, 
    SUBSTRING(
        (SELECT ', ' + Valore
            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;

e tutto funziona correttamente lo stesso...

>Un'altra cosa, la funzione FOR XML PATH fa automaticamente un'iterazione sul campo per concatenare i risultati?


questa e' una tecnica che consente di iterare per ogni codice filtrato restituendo un insieme (grazie a XML) di colonne, in questo caso separate da "," (CSV) [grazie al suffisso ', ' + Valore] quindi "valore1, valore2, valoreN" permettendo quindi "l'aggregazione" desiderata..
la sinossi (anche se un po' ridotta) la trovi in https://msdn.microsoft.com/it-it/library/bb510462.aspx ->Creazione di un elenco di valori mediante la modalità PATH

dalla sua introduzione, e' ovviamente stata subito utilizzata non gia' in ambito XML ma direttamente in ambito tradizionale proprio per eseguire questa tipologia di operazioni...
saluti

Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php
384 messaggi dal 26 agosto 2007
Grazie infinite!! Mi sei stato non di aiuto ma di più!!

Lunga vita e prosperità!!

Torna al forum | Feed RSS

ASPItalia.com non è responsabile per il contenuto dei messaggi presenti su questo servizio, non avendo nessun controllo sui messaggi postati nei propri forum, che rappresentano l'espressione del pensiero degli autori.