Tabel is:
| ID | Naam |
|---|---|
| 1 | aaa |
| 1 | bbb |
| 1 | ccc |
| 1 | ddd |
| 1 | eee |
Antwoord 1, autoriteit 100%
Zo werkt het:
1. XML-elementstring ophalen met FOR XML
Door FOR XML PATH aan het einde van een query toe te voegen, kunt u de resultaten van de query uitvoeren als XML-elementen, met de elementnaam in het PATH-argument. Als we bijvoorbeeld de volgende instructie zouden uitvoeren:
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
Door een lege string (FOR XML PATH(”)) door te geven, krijgen we in plaats daarvan het volgende:
,aaa,bbb,ccc,ddd,eee
2. Voorloopkomma verwijderen met STUFF
De STUFF-instructie “vult” letterlijk de ene tekenreeks in de andere, waarbij tekens in de eerste tekenreeks worden vervangen. We gebruiken het echter gewoon om het eerste teken van de resulterende lijst met waarden te verwijderen.
SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
De parameters van STUFFzijn:
- De tekenreeks die moet worden gevuld (in ons geval de volledige lijst met namen met a
voorloopkomma) - De locatie om te beginnen met het verwijderen en invoegen van tekens (1, we vullen een lege string in)
- Het aantal tekens dat moet worden verwijderd (1, de eerste komma)
Dus we eindigen met:
aaa,bbb,ccc,ddd,eee
3. Word lid op id om de volledige lijst te krijgen
Vervolgens voegen we dit toe aan de lijst met ID’s in de tijdelijke tabel, om een lijst met ID’s met naam te krijgen:
SELECT ID, abc = STUFF(
(SELECT ',' + name
FROM temp1 t1
WHERE t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') from temp1 t2
group by id;
En we hebben ons resultaat:
| ID | Naam |
|---|---|
| 1 | aaa,bbb,ccc,ddd,eee |
Antwoord 2, autoriteit 12%
Dit artikelbehandelt verschillende manieren om strings in SQL samen te voegen, inclusief een verbeterde versie van uw code die de aaneengeschakelde waarden niet XML-codeert.
SELECT ID, abc = STUFF
(
(
SELECT ',' + name
FROM temp1 As T2
-- You only want to combine rows for a single ID here:
WHERE T2.ID = T1.ID
ORDER BY name
FOR XML PATH (''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM temp1 As T1
GROUP BY id
Om te begrijpen wat er gebeurt, begin met de innerlijke vraag:
SELECT ',' + name
FROM temp1 As T2
WHERE T2.ID = 42 -- Pick a random ID from the table
ORDER BY name
FOR XML PATH (''), TYPE
Omdat u FOR XMLopgeeft, krijgt u een enkele rij met een XML-fragment dat alle rijen vertegenwoordigt.
Omdat je geen kolomalias hebt opgegeven voor de eerste kolom, zou elke rij worden ingepakt in een XML-element met de naam die tussen haakjes is opgegeven na het FOR XML PATH. Als u bijvoorbeeld FOR XML PATH ('X')had, zou u een XML-document krijgen dat er als volgt uitziet:
<X>,aaa</X>
<X>,bbb</X>
...
Maar aangezien u geen elementnaam hebt opgegeven, krijgt u alleen een lijst met waarden:
,aaa,bbb,...
De .value('.', 'varchar(max)')haalt eenvoudig de waarde uit het resulterende XML-fragment, zonder XML-codering van “speciale” tekens. Je hebt nu een string die eruitziet als:
',aaa,bbb,...'
De functie STUFFverwijdert vervolgens de eerste komma, waardoor u een eindresultaat krijgt dat er als volgt uitziet:
'aaa,bbb,...'
Het ziet er op het eerste gezicht nogal verwarrend uit, maar het presteert best goed in vergelijking met sommige andere opties.
Antwoord 3, autoriteit 7%
PATH-modus wordt gebruikt bij het genereren van XML uit een SELECT-query
1. SELECT
ID,
Name
FROM temp1
FOR XML PATH;
Ouput:
<row>
<ID>1</ID>
<Name>aaa</Name>
</row>
<row>
<ID>1</ID>
<Name>bbb</Name>
</row>
<row>
<ID>1</ID>
<Name>ccc</Name>
</row>
<row>
<ID>1</ID>
<Name>ddd</Name>
</row>
<row>
<ID>1</ID>
<Name>eee</Name>
</row>
De uitvoer is elementgerichte XML waarbij elke kolomwaarde in de resulterende rijenset is verpakt in een rij-element. Omdat de SELECT-component geen aliassen specificeert voor de kolomnamen, zijn de gegenereerde namen van onderliggende elementen dezelfde als de corresponderende kolomnamen in de SELECT-component.
Voor elke rij in de rijenset wordt een tag toegevoegd.
2.
SELECT
ID,
Name
FROM temp1
FOR XML PATH('');
Ouput:
<ID>1</ID>
<Name>aaa</Name>
<ID>1</ID>
<Name>bbb</Name>
<ID>1</ID>
<Name>ccc</Name>
<ID>1</ID>
<Name>ddd</Name>
<ID>1</ID>
<Name>eee</Name>
Voor Stap 2: Als u een string met een lengte van nul opgeeft, wordt het wikkelelement niet geproduceerd.
3.
SELECT
Name
FROM temp1
FOR XML PATH('');
Ouput:
<Name>aaa</Name>
<Name>bbb</Name>
<Name>ccc</Name>
<Name>ddd</Name>
<Name>eee</Name>
4. SELECT
',' +Name
FROM temp1
FOR XML PATH('')
Ouput:
,aaa,bbb,ccc,ddd,eee
In stap 4 voegen we de waarden samen.
5. SELECT ID,
abc = (SELECT
',' +Name
FROM temp1
FOR XML PATH('') )
FROM temp1
Ouput:
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
6. SELECT ID,
abc = (SELECT
',' +Name
FROM temp1
FOR XML PATH('') )
FROM temp1 GROUP by iD
Ouput:
ID abc
1 ,aaa,bbb,ccc,ddd,eee
In stap 6 groeperen we de datum op ID.
STUFF( source_string, start, length, add_string )
Parameters of argumenten
source_string
De brontekenreeks die moet worden gewijzigd.
begin
De positie in de source_string om lengtetekens te verwijderen en vervolgens add_string in te voegen.
lengte
Het aantal tekens dat moet worden verwijderd uit source_string.
add_string
De reeks tekens die moet worden ingevoegd in de source_string op de startpositie.
SELECT ID,
abc =
STUFF (
(SELECT
',' +Name
FROM temp1
FOR XML PATH('')), 1, 1, ''
)
FROM temp1 GROUP by iD
Output:
-----------------------------------
| Id | Name |
|---------------------------------|
| 1 | aaa,bbb,ccc,ddd,eee |
-----------------------------------
Antwoord 4, autoriteit 4%
Er is zeer nieuwe functionaliteit in Azure SQL Database en SQL Server (vanaf 2017) om dit exacte scenario af te handelen. Ik geloof dat dit zou dienen als een native officiële methode voor wat je probeert te bereiken met de XML/STUFF-methode. Voorbeeld:
select id, STRING_AGG(name, ',') as abc
from temp1
group by id
STRING_AGG – https://msdn.microsoft.com/en-us/ bibliotheek/mt790580.aspx
BEWERK:Toen ik dit oorspronkelijk plaatste, maakte ik melding van SQL Server 2016 omdat ik dacht dat ik dat zag op een mogelijke functie die zou worden opgenomen. Of ik herinnerde me dat verkeerd of er is iets veranderd, bedankt voor de voorgestelde bewerking om de versie te herstellen. Ik was ook behoorlijk onder de indruk en was niet volledig op de hoogte van het beoordelingsproces in meerdere stappen dat me zojuist naar een laatste optie heeft getrokken.
Antwoord 5
In for xml path, als we een waarde definiëren zoals [ for xml path('ENVLOPE') ], dan worden deze tags aan elke rij toegevoegd:
<ENVLOPE>
</ENVLOPE>
Antwoord 6
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
)
FROM temp1 GROUP BY id
Hier in de bovenstaande query wordt de functie STUFFgebruikt om de eerste komma (,)te verwijderen uit de gegenereerde xml-tekenreeks (,aaa,bbb,ccc,ddd,eee)dan wordt het (aaa,bbb,ccc,ddd,eee).
En FOR XML PATH('')converteert eenvoudig kolomgegevens naar (,aaa,bbb,ccc,ddd,eee)string maar in PATHwe passeren ” dus er wordt geen XML-tag gemaakt.
En aan het einde hebben we records gegroepeerd met behulp van de kolom ID.
Antwoord 7
Ik heb debuggen gedaan en uiteindelijk mijn ‘gevulde’ zoekopdracht teruggestuurd op de normale manier.
Gewoon
select * from myTable for xml path('myTable')
geeft me de inhoud van de tabel om naar een logtabel te schrijven vanuit een trigger die ik debug.
Antwoord 8
Declare @Temp As Table (Id Int,Name Varchar(100))
Insert Into @Temp values(1,'A'),(1,'B'),(1,'C'),(2,'D'),(2,'E'),(3,'F'),(3,'G'),(3,'H'),(4,'I'),(5,'J'),(5,'K')
Select X.ID,
stuff((Select ','+ Z.Name from @Temp Z Where X.Id =Z.Id For XML Path('')),1,1,'')
from @Temp X
Group by X.ID
Antwoord 9
STUFF((SELECT distinct ‘,’ + CAST(T.ID) FROM Table T waarbij T.ID=1 FOR XML PATH(”)),1,1,”) AS Name