String_AGG

Jeg fortsætter min ”undersøgelse” af de mange funktioner i SQL Serveren og denne gang er det string funktionen STRING_AGG, som jeg vil lege lidt med. STRING_AGG blev tilføjet T-SQL sproget, da SQL server 2017 udkom.

Syntax er:

STRING_AGG ( expression, separator ) [ <order_clause> ]
  <order_clause> ::=
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

Expression er en text string enten varchar eller nvarchar. Hvis funktionen får en anden datatype, så konverteres den efter de gældende regler ved cast / convert. Hvis input (expression) er varchar så kan seperatoren ikke være nvarchar, dette vil udløse en fejl.

Seperator er en karakter efter eget valg, som benyttes som seperator (kommer ikke på efter den sidste). Det er også muligt at bestemme sorteringsrækkefølgen med within group delen af syntaxen. Den vender jeg tilbage til.

Lad os prøve at kigge på et par eksempler; jeg benytter Person tabellen fra Adventureworks databasen, har fat i version 2016ctp, men dette burde virke på de andre også. Først opretter jeg en tabel, som jeg kan arbejde på, jeg tilføjer en fødselsdato (birthdate) som jeg skal bruge senere.

Query:

USE Demo;
GO

DROP TABLE IF EXISTS dbo.Persons;
GO

CREATE TABLE dbo.Persons(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Title VARCHAR(4),
    Firstname VARCHAR(40),
    Lastname VARCHAR(40),
    Birthday DATE
);
GO

INSERT INTO dbo.Persons(Title, Firstname,Lastname,Birthday)
SELECT Title,
       FirstName,
       LastName,
       (
            SELECT  
                CAST(DATEADD(SECOND, ROUND(((DATEDIFF(SECOND, '1980-01-01', '2015-03-05')-1) * RAND(p.BusinessEntityID)), 0), '1980-01-01') AS DATE) As dob
            FROM AdventureWorks2016CTP3.person.Person AS pp
            WHERE p.BusinessEntityID = pp.BusinessEntityID        
        ) AS Birthday
FROM AdventureWorks2016CTP3.Person.Person AS p
WHERE p.Title IS NOT NULL;
GO
For at begrænse mængden af data, har jeg et prædikat på at Title ikke skal være NULL. Dette giver mig 1009 rækker i tabellen.
Data ser sådan her ud:
SELECT *
FROM dbo.Persons;
GO

Nu hvor data er klar, lad os prøve STRING_AGG funktionen af.

SELECT STRING_AGG(Firstname,';') name_string       
FROM dbo.Persons AS p

Opmærksom på begrænset data

Vi skal være opmærksomme på at STRING_AGG ikke kan indeholde uendelige mængder data der er følgende begrænsninger.

Vi får altså en fejl hvis datamængden bliver for stor.

SELECT STRING_AGG(CONCAT(Firstname,' ' , Lastname),';') name_string       
FROM dbo.Persons AS p

Giver denne fejl:

Vi skal være opmærksomme på, at vi skal begrænse os hvis vi gerne vil benytte eksempelvis CONCAT sammen med STRING_AGG.

Hvis vi udvider vores Query fra før og benytter GROUP BY, så får vi delt vores resultat ud efter en given gruppe f.eks.:

SELECT  P.Title,
        STRING_AGG(CONCAT(Firstname,' ' , Lastname),';') name_string       
      FROM dbo.Persons AS p
GROUP BY p.Title

Så er der nu nok plads til at benytte CONCAT sammen med STRING_AGG.

Within Group

WITHIN GROUP er en mulighed for at bestemme en bestemt sorteringsorden inde i vores STRING_AGG. Hvis vi ændrer vores Query så vi får grupperne sorteret efter fornavn stigende; det vil se sådan her ud:

SELECT  P.Title,
        STRING_AGG(CONCAT(Firstname,' ' , Lastname),';') 
        WITHIN GROUP (ORDER BY FirstName) AS name_string 
FROM dbo.Persons AS p
GROUP BY p.Title

Vær opmærksom på, at hvis du vælger at sortere data med WITHIN GROUP, så tilføjes en sort operator til vores executionplan. Her ses 2 planer; den første uden sortering og derefter den anden med sortering i WITHIN GROUP:

Så sorter kun data hvis det er vigtigt for dit resultat sæt.

Kan vi gøre det samme ved blot at tilføje en ORDER BY på vores Query? … Nej, for det første så kan vi kun ORDER BY de kolonner vi har med i vores GROUP BY, og desuden skal vi være opmærksomme på ”order of execution” af et SQL Query, hvor SELECT kommer før ORDER BY, altså stringen bygges før resultatet sorteres.

Husk at hvis input datatypen (expression) er varchar, så kan du IKKE benytte en nvarchar som seperator, se følgende Query:

SELECT STRING_AGG(CONCAT(Firstname, ' ' , Lastname),N';') name_string,
       DATEDIFF(YEAR, MIN(p.Birthday), GETDATE()) Oldest_in_group
FROM dbo.Persons AS p
WHERE LEN(p.Firstname) = 3
GROUP BY p.Title;
GO

Tabellen er Firstname og Lastname oprettet som varchar og ved at sætte N foran ’;’ fortæller vi, at det er en nvarchar, Query vil give dette resultat.

Husk at når du arbejder med STRING_AGG så arbejdes der på en gruppe af rækker, hvis du ikke benytter GROUP BY så er alle rækker i tabellen 1 gruppe, hvilket gør at du får en fejl hvis du forsøger at tage fat i andre kolonner, som ikke er med i en GROUP BY.

Se følgende Query:

SELECT STRING_AGG(CONCAT(Firstname, ' ' , Lastname),';') name_string,
       p.Birthday
FROM dbo.Persons AS p
GROUP BY p.Title;
GO

Giver dette resultat:

Aggregate funktion

Hvis vi derimod benytter en AGGREGATE funktion så må vi godt, men her skal vi være særligt opmærksomme på om det er det ønskede resultat vi får, hvis vi f.eks. vil finde den ældste i en gruppe, kan vi gøre følgende:

SELECT STRING_AGG(CONCAT(Firstname, ' ' , Lastname),';') name_string,
       DATEDIFF(YEAR, MIN(p.Birthday), GETDATE()) Oldest_in_group
FROM dbo.Persons AS p
GROUP BY p.Title;
GO

Hvis du derimod var interesseret i alderen på hver enkelt, så er du nødt til at sætte det sammen med stringen, som kommer med i STRING_AGG:

(jeg har sat en ekstra WHERE på, for ikke at stringen skulle blive for stor)

Det var alt for denne gang, som altid så er du velkommen til at kontakte mig eller en af mine dygtige kollegaer i Unit IT på tlf.: 88 333 333 hvis du har spørgsmål eller udfordringer med dit SQL miljø.