valore - sql unire due righe




Come concatenare il testo da più righe in una singola stringa di testo nel server SQL? (20)

Questa risposta può restituire risultati imprevisti quando è presente una clausola ORDER BY. Per risultati coerenti, utilizzare uno dei metodi PATH FOR XML dettagliati in altre risposte.

Usa COALESCE :

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

Solo alcune spiegazioni (poiché questa risposta sembra avere visualizzazioni relativamente regolari):

  • Coalesce è davvero solo un trucco utile che realizza due cose:

1) Non è necessario inizializzare @Names con un valore di stringa vuoto.

2) Non è necessario rimuovere un separatore aggiuntivo alla fine.

  • La soluzione sopra darà risultati errati se una riga ha un valore NULL Name (se c'è un NULL , il NULL renderà @Names NULL dopo quella riga, e la riga successiva ricomincerà come una stringa vuota. di due soluzioni:
DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

o:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

A seconda del comportamento che si desidera (la prima opzione filtra solo NULL , la seconda opzione li mantiene nell'elenco con un messaggio marker [sostituisce 'N / A' con qualsiasi cosa sia appropriata per te]).

Prendi in considerazione una tabella del database contenente nomi, con tre righe:

Peter
Paul
Mary

C'è un modo semplice per trasformare questo in una singola stringa di Peter, Paul, Mary ?


SQL Server 2017+ e SQL Azure: STRING_AGG

A partire dalla prossima versione di SQL Server, possiamo finalmente concatenare le righe senza dover ricorrere a variabili o XML witchery.

STRING_AGG (Transact-SQL)

Senza raggruppamento

SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

Con il raggruppamento:

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

Con raggruppamento e ordinamento secondario

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department 
GROUP BY GroupName;

È necessario creare una variabile che manterrà il risultato finale e selezionarlo, in questo modo.

Soluzione più semplice

DECLARE @char VARCHAR(MAX);

SELECT @char = COALESCE(@char + ', ' + [column], [column]) 
FROM [table];

PRINT @char;

È stata suggerita una soluzione CTE ricorsiva, ma nessun codice fornito. Il codice riportato di seguito è un esempio di CTE ricorsivo: si noti che, sebbene i risultati corrispondano alla domanda, i dati non corrispondono esattamente alla descrizione fornita, poiché presumo che si desideri eseguire questa operazione su gruppi di righe, non tutti righe nella tabella. Cambiarlo per abbinare tutte le righe nella tabella è lasciato come esercizio per il lettore.

;with basetable as 
(   SELECT id, CAST(name as varchar(max))name, 
        ROW_NUMBER() OVER(Partition By id     order by seq) rw, 
        COUNT(*) OVER (Partition By id) recs 
FROM (VALUES (1, 'Johnny', 1), (1,'M', 2), 
                  (2,'Bill', 1), (2, 'S.', 4), (2, 'Preston', 5), (2, 'Esq.', 6),
        (3, 'Ted', 1), (3,'Theodore', 2), (3,'Logan', 3),
                  (4, 'Peter', 1), (4,'Paul', 2), (4,'Mary', 3)

           )g(id, name, seq)
),
rCTE as (
    SELECT recs, id, name, rw from basetable where rw=1
    UNION ALL
    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw+1
    FROM basetable b
         inner join rCTE r
    on b.id = r.id and b.rw = r.rw+1
)
SELECT name FROM rCTE
WHERE recs = rw and ID=4

Anche questo può essere utile

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

ritorna

Peter,Paul,Mary

Con le altre risposte, la persona che legge la risposta deve essere a conoscenza di una tabella di dominio specifica come veicolo o studente. La tabella deve essere creata e popolata con i dati per testare una soluzione.

Di seguito è riportato un esempio che utilizza la tabella "Information_Schema.Columns" di SQL Server. Utilizzando questa soluzione, non è necessario creare tabelle o dati aggiunti. Questo esempio crea un elenco separato da virgole di nomi di colonne per tutte le tabelle nel database.

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 

Gli array Postgres sono fantastici. Esempio:

Creare alcuni dati di test:

postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE                                      
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');                                                          
INSERT 0 3
test=# select * from names;
 name  
-------
 Peter
 Paul
 Mary
(3 rows)

Aggregali in un array:

test=# select array_agg(name) from names;
 array_agg     
------------------- 
 {Peter,Paul,Mary}
(1 row)

Converti l'array in una stringa delimitata da virgole:

test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

FATTO

Dal momento che PostgreSQL 9.0 è ancora più semplice .


In MySQL esiste una funzione, Group_Concat , che consente di concatenare i valori da più righe. Esempio:

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a

In SQL Server 2005 e versioni successive, utilizzare la query seguente per concatenare le righe.

DECLARE @t table
(
    Id int,
    Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 

SELECT ID,
stuff(
(
    SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t


Mi è davvero piaciuta l'eleganza della risposta di Dana . Volevo solo che fosse completo.

DECLARE @names VARCHAR(MAX)
SET @names = ''

SELECT @names = @names + ', ' + Name FROM Names 

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

Non ho accesso a un server SQL a casa, quindi qui indovino la sintassi, ma è più o meno:

DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names

Per evitare valori nulli puoi usare CONCAT ()

DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name) 
FROM Names
select @names

Per i DB Oracle, vedere questa domanda: come concatenare più righe in una in Oracle senza creare una stored procedure?

La migliore risposta sembra essere di @Emmanuel, usando la funzione LISTAGG () integrata, disponibile in Oracle 11g Release 2 e successive.

SELECT question_id,
   LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id

come sottolineato @ user762952, e secondo la documentazione di Oracle http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php , la funzione WM_CONCAT () è anche un'opzione. Sembra stabile, ma Oracle raccomanda espressamente di non utilizzarlo per alcuna applicazione SQL, quindi usarlo a proprio rischio.

Oltre a questo, dovrai scrivere la tua funzione; il documento Oracle sopra ha una guida su come farlo.


Questo metodo si applica al database Teradata Aster solo quando utilizza la sua funzione NPATH.

Ancora una volta, abbiamo studenti da tavolo

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Quindi con NPATH è solo SELEZIONA:

SELECT * FROM npath(
  ON Students
  PARTITION BY SubjectID
  ORDER BY StudentName
  MODE(nonoverlapping)
  PATTERN('A*')
  SYMBOLS(
    'true' as A
  )
  RESULT(
    FIRST(SubjectID of A) as SubjectID,
    ACCUMULATE(StudentName of A) as StudentName
  )
);

Risultato:

SubjectID       StudentName
----------      -------------
1               [John, Mary, Sam]
2               [Alaina, Edward]

Se si utilizza SQL Server 2017 o Azure, consultare la risposta di Mathieu Renda .

Ho avuto un problema simile quando stavo cercando di unire due tabelle con relazioni uno-a-molti. In SQL 2005 ho scoperto che il metodo XML PATH può gestire la concatenazione delle righe molto facilmente.

Se c'è un tavolo chiamato STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Il risultato che mi aspettavo era:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

Ho usato il seguente T-SQL :

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

Puoi fare la stessa cosa in un modo più compatto se puoi concatenare le virgole all'inizio e usare la substring per saltare la prima in modo da non dover eseguire una sottocompilazione:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Un metodo non ancora mostrato tramite il comando XML data() in MS SQL Server è:

Assumi tabella denominata NameList con una colonna denominata FName,

SELECT FName + ', ' AS 'data()' 
FROM NameList 
FOR XML PATH('')

ritorna:

"Peter, Paul, Mary, "

Solo la virgola extra deve essere gestita.

Modifica: come adottato dal commento di @ NReilingh, è possibile utilizzare il seguente metodo per rimuovere la virgola finale. Assumendo gli stessi nomi di tabelle e colonne:

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

Una soluzione pronta all'uso, senza virgole extra:

select substring(
        (select ', '+Name AS 'data()' from Names for xml path(''))
       ,3, 255) as "MyList"

Una lista vuota genererà un valore NULL. Solitamente inserirai la lista in una colonna della tabella o in una variabile del programma: regola la lunghezza massima 255 secondo le tue necessità.

(Diwakar e Jens Frandsen hanno fornito buone risposte, ma hanno bisogno di miglioramenti).


MySQL completo Esempio:

Abbiamo utenti che possono avere molti dati e vogliamo avere un output, in cui possiamo vedere tutti i dati degli utenti in un elenco:

Risultato:

___________________________
| id   |  rowList         |
|-------------------------|
| 0    | 6, 9             |
| 1    | 1,2,3,4,5,7,8,1  |
|_________________________|

Impostazione tabella:

CREATE TABLE `Data` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);


CREATE TABLE `User` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `User` (`id`) VALUES
(0),
(1);

Query:

SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id

DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

Questo mette la virgola vagante all'inizio.

Tuttavia, se hai bisogno di altre colonne o di CSV una tabella figlio, devi avvolgerla in un campo definito dall'utente scalare (UDF).

Puoi usare il percorso XML come sottoquery correlata anche nella clausola SELECT (ma dovrei aspettare fino al mio ritorno al lavoro perché Google non fa cose da lavoro a casa :-)





group-concat