DECLARE @Table Table (Id int Not NULL identity(1,1), Wert nvarchar(10))
Insert into @Table Values('A'),('B'),('C')
Select * from @Table
Select STRING_AGG(Wert,', ') WITHIN GROUP ( ORDER BY Wert ASC) as Liste from @Table
Kommaseparierte Liste – Explode (string_split)
Seit SQL Server 2016 gibt es einige neue Funktionen. Ein Beispiel mit dem Code wesentlich kürzer ist die table based function „string_split()“.
Diese Funktion wird als Tabelle aufgerufen und hat nur die Spalte „value“:
/*Test Tabelle und Daten bereitstellen:*/
DECLARE @Test TABLE (id int identity(1,1) NOT NULL, wert nvarchar(MAX) NULL)
INSERT INTO @test VALUES ('a,b,c,d,e,f'), ('1,2,3,4,5,6,7,8,9'), ('aa,bb,cc,dd,ee,ff,gg')
--Select * from @Test
/*Daten aus einem Feld als Tabelle bereitstellen:*/
Select value
from @Test
outer apply string_split(wert,',')
where id = 3
Kommaseparierte Liste – Implode (XML)
SELECT
a.AccountID
,STUFF(
(SELECT '; ' + Coalesce(Lastname,'')+Coalesce(', '+Firstname,'')
FROM Contact c
Where c.AccountID = a.AccountID
FOR XML PATH('')
)
, 1, 2, '')
As concatenated_string
FROM Account a
order by a.accountid
Hinweis: Falls der Ergebnis-String nicht größer wird als 4000 bzw. 8000 Zeichen, dann könnte auch „STRING_AGG“ benutzt werden!
Kommaseparierte Liste – Implode (Funktion)
Beispiel Code um aus mehreren Sätze eine „Kommaseparierte Liste“ zu haben:
IF EXISTS (select name from sysobjects where name = 'fod_implode' and Type = 'FN') Drop function fod_implode
CREATE FUNCTION sysdba.fod_implode ( @id AS char(12))
RETURNS varchar(4000)
BEGIN
DECLARE @implode varchar(3000)
Set @implode = ''
DECLARE Name_Cursor CURSOR FOR
SELECT lastname FROM sysdba.Contact WHERE accountid = @id
DECLARE @Value varchar(255)
OPEN Name_Cursor
FETCH NEXT FROM Name_Cursor into @Value
WHILE (@@FETCH_STATUS = 0)
BEGIN
if @implode = ''
Set @implode = @Value
else
Set @implode = @implode + ', ' + @Value
FETCH NEXT FROM Name_Cursor into @Value
END
CLOSE Name_Cursor
DEALLOCATE Name_Cursor
--Rueckgabewert:
RETURN @implode
END
Kommaseparierte Liste – Explode (Rekursiv)
Beispiel Code um die Werte aus einem einzelnem Feld als Tabelle bereitzustellen:
/*
Felder die Semikolon separierte Felder enthalten, sollen diese in Tabelle mit entsprechend vielen Zeilen sein
*/
Create Table #t (Id int unique, value varchar(254))
Insert into #t Values(1, 'a;bb;ccc;')
Insert into #t Values(2, 'x;yy')
Insert into #t Values(3, NULL)
/*Erst an das Ende Semikolons anhängen*/
Update #t set value=rtrim(value)+';' Where right(rtrim(value),1)<>';' and (value<>'')
--Select * from #t
;With ht(Id, v, Pos)
As (
/*Anker*/
Select id, Substring(value,1,CHARINDEX(';',value,1)-1), CHARINDEX(';',value,1)-1 as Pos
From #t
Where CHARINDEX(';',value,3)>0
union all
/*Recursion*/
Select #t.Id, Substring(#t.value,ht.Pos+2,CHARINDEX(';',value,ht.Pos+2)-1 - ht.pos - 1), CHARINDEX(';',value,ht.Pos+2)-1 as Pos
From #t
inner join ht on #t.Id = ht.Id
Where CHARINDEX(';',#t.value, (ht.Pos+2)) > 0
)
Select *
from ht
Drop Table #t