Aggregatfunktion für Texte mit GROUP BY

[sqlrookie]

Hallo Zusammen,

habe eine Frage die wahrscheinlich schon einige Male aufgetaucht ist.

Ich brauche eine SQL Abfrage die bei Verwendung der GROUP BY Klausel, den Text einer Spalte verkettet. Ich probiere es die ganze Zeit mit CONCAT, komme aber leider zu keinem Ergebnis. Ich finde unzählige Beispiele für die Verkettung zweier Spalten (ist auch kein Problem) aber speziell für diesen Fall finde ich nichts. Nachfolgend ein kleines Beispiel.

Abteilung Name
1 Ernst
2 August
1 Wilhelm

Ergebnis der Abfrage soll sein:

Abteilung Expr1
1 Ernst/Wilhelm
2 August

Vielen Dank für eure Hilfe

[hwoess]

Da brauchst du eine UDF dazu, die kannst du nämlich auch in einem SELECT verwenden. Ich habe da ein Beispiel von früher ausgegraben, dass ich hier schon mal irgendwann reingeschrieben habe (finde aber den Eintrag nicht mehr)

use tempdb
go

create table t1 (id int)
create table t2 (id int, ort varchar(20))

go

insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
insert into t1 values(4)

insert into t2 values(1,'Linz')
insert into t2 values(1,'Wien')
insert into t2 values(2,'Berlin')
insert into t2 values(2,'Hamburg')
insert into t2 values(2,'Bonn')
insert into t2 values(4,'Zürich')

go

create function leseOrte (@id int)
returns varchar(2000) as
begin
declare @orte varchar(2000)
select @orte = IsNull(@orte + ', ','') + ort from t2 where id = @id
return @orte
end

go

select id, dbo.leseOrte(id) from t1

drop function leseOrte
drop table t2
drop table t1

Damit sollte es dir möglich sein, dein Problem zu lösen

bye,
Helmut

[O. Helper]

Wenn es auch über eine (temporäre) Zwischentabelle laufen darf:
Bin ich letztens zufällig drüber gestolpert, nennt sich Quirky update in sql server; der passende Teil steht ganz zuletzt.

Echt schräg und funktioniert sogar ... für diesen Fall, ... bei den anderen Demos eher nur fast, da man bei Updates leider kein ORDER BY angeben.

[ebis]

Wenn man ab SQl-2005 die FOR XML Klausel missbraucht,
gehts auch ohne Function und temporäre Table Variable
[HIGHLIGHT="SQL"]BEGIN TRAN;
USE tempdb;
CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int, ort varchar(20)) ;

INSERT INTO t1 SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4;

INSERT INTO t2 SELECT 1,'Linz'
UNION ALL SELECT 1,'Wien'
UNION ALL SELECT 2,'Berlin'
UNION ALL SELECT 2,'Hamburg'
UNION ALL SELECT 2,'Bonn'
UNION ALL SELECT 4,'Zürich';

SELECT LEFT (Y.orte, LEN (Y.orte) -1)
FROM t1
CROSS APPLY (
SELECT x.orte AS [text()]
FROM
(
SELECT ort + NCHAR(44) +SPACE(1) AS orte
FROM t2
WHERE t1.ID = T2.ID
) AS X
FOR XML PATH ('')
) AS Y (orte);
ROLLBACK; [/HIGHLIGHT]

[hwoess]

Irgendwie ganz interessant, aber machen wir jetzt den nächsten Schritt und versuchen dasselbe Ergebnis nur aus Tabelle t2 zu erzielen. Jetzt brauchen wir also ein group by....
Bei meinem Beispiel würde sich das so zeigen:

statt

...
select id, dbo.leseOrte(id) from t1
...

schreibe ich

...
select id, dbo.leseOrte(id) from t2
group by id, dbo.leseOrte(id)
...

Wie sieht das dann mit dem CROSS APPLY aus, wenn es wie vom Fragesteller vorgegeben keine Tabelle t1 gibt, sondern nur eine t2, aus der die ID's eben über das group by ermittelt werden?

bye,
Helmut

[ebis]

Wie sieht das dann mit dem CROSS APPLY aus, wenn es wie vom Fragesteller vorgegeben keine Tabelle t1 gibt, sondern nur eine t2, aus der die ID's eben über das group by ermittelt werden?

so:[HIGHLIGHT="SQL"]BEGIN TRAN;
USE tempdb;
CREATE TABLE Test (id int, ort varchar(20)) ;

INSERT INTO Test SELECT 1,'Linz'
UNION ALL SELECT 1,'Wien'
UNION ALL SELECT 2,'Berlin'
UNION ALL SELECT 2,'Hamburg'
UNION ALL SELECT 2,'Bonn'
UNION ALL SELECT 4,'Zürich';

SELECT DISTINCT
LEFT (Y.orte, LEN (Y.orte) -1) AS orte
, TestOuter.ID
FROM Test AS TestOuter
CROSS APPLY (
SELECT x.orte AS [text()]
FROM
(
SELECT ort + NCHAR(44) +SPACE(1) AS orte
FROM Test AS Testinner
WHERE TestOuter.id = TestInner.ID
) AS X
FOR XML PATH ('')
) AS Y (orte)

ROLLBACK; [/HIGHLIGHT]

[ebis]

Man kann auch statt des DISTINCT ein GROUP BY machen:
[HIGHLIGHT="SQL"]BEGIN TRAN;
USE tempdb;
CREATE TABLE Test (id int, ort varchar(20)) ;

INSERT INTO Test SELECT 1,'Linz'
UNION ALL SELECT 1,'Wien'
UNION ALL SELECT 2,'Berlin'
UNION ALL SELECT 2,'Hamburg'
UNION ALL SELECT 2,'Bonn'
UNION ALL SELECT 4,'Zürich';

SELECT MAX((LEFT (Y.orte, LEN (Y.orte) -1))) AS orte
, TestOuter.ID
FROM Test AS TestOuter
CROSS APPLY (
SELECT x.orte AS [text()]
FROM
(
SELECT ort + NCHAR(44) +SPACE(1) AS orte
FROM Test AS Testinner
WHERE TestOuter.id = TestInner.ID
) AS X
FOR XML PATH ('')
) AS Y (orte)
GROUP BY id

ROLLBACK; [/HIGHLIGHT]

[hwoess]

Nicht schlecht, muss ich mir gleich mal notieren

bye,
Helmut

[sqlrookie]

Hallo nochmal,

danke für die Antworten. Ich hab mich wahrscheinlich falsch bzw. ungenau ausgedrückt. Hier nun der konkrete Anwendungsfall:

Tabelle 1: "ref"

ID (UniqueID)
ref (Referenzname)
... (weitere Felder)

Tabelle 2: "b2r"

ID_r (ID aus Tabelle 1)
ID_b (ID aus Tabelle 3)

Tabelle 3: "branches"

ID (UniqueID)
branche_de (Branchenname deutsch)
branche_en (Branchenname englisch)

Tabelle 2 ist eine reine Zuordnungstabelle. Ich habe den Fall, dass zu einem Eintrag
in der Tabelle1 "ref" mehrere (unbekannte Anzahl) Einträge der Tabelle3 "branches" existieren können.

In einem GridView möchte ich später pro Eintrag in Tabelle 1 "ref" eine Zeile generieren
und mit Hilfe einer solchen Abfrage hier, in einem TemplateField alle zugeordneten Branchen durch Komma abgetrennt darstellen.

Vielen Dank für Eure Mühe!

[hwoess]

Ich bleibe mal bei meiner einfachen UDF:

create function leseBranchen (@id int)
returns varchar(2000) as
begin
declare @branchen varchar(2000)
select @branchen = IsNull(@branchen + ', ','') + branche_de from branches
where id in (select ID_b from b2r where ID_r = @id)
return @branchen
end

.. und Abfrage wie gehabt:

select id, dbo.leseBranchen(id) from ref

bye,
Helmut

[sqlrookie]

Hallo Helmut,

vielen Dank, funzt einwandfrei!!!

Grüße