CODEWITHSUNDEEP
sql-servert-sqlaggregatefor-xml-path
George Menoutis
George Menoutis

Reputation: 7260

XML path aggregation: one subquery for multiple `selects` on same subset

When using XML path for aggregation, many times I need different strings which are based on the same set. Consider this example (script at the end):

+-----------+-----------+------------+
| firstname |   thing   |    val     |
+-----------+-----------+------------+
| mike      | Tesla     |      50000 |
| mike      | Mona Lisa | 3000000000 |
+-----------+-----------+------------+

I want this result set returned:

+-----------+---------------------------------------------+------------------------------------------------------------------------+
| firstname |            what_I_say_to_friends            |                         what_I_say_to_finance                          |
+-----------+---------------------------------------------+------------------------------------------------------------------------+
| Mike      | My friend Mike owns a Tesla and a Mona Lisa | My friend Mike owns a Tesla priced 50000 and a Mona Lisa priced 3e+009 |
+-----------+---------------------------------------------+------------------------------------------------------------------------+

I know how to do this using two different xml-path subqueries. But, since the only thing that changes is the choice of data to show (select), and not the underlying rows, is there any way to do it with a single xml path?

Data construction & dual xml query:

create table friend (   firstname nvarchar(50) )

insert friend values ('Mike')

create table owns
(
    firstname nvarchar(50)
    ,thing nvarchar(50)
    ,val float
)

insert  owns values
('mike','Tesla',50000),('mike','Mona Lisa',3000000000)

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a '+q1.collection_no_value as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a '+q2.collection_with_value as what_I_say_to_finance
from 
    friend f
    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + o.thing
                    from owns o
                    where o.firstname=f.firstname
                    FOR XML PATH(''), TYPE
                ).value('.','nvarchar(max)')
                ,1,7,''
            ) as collection_no_value
    ) as q1
cross apply
(
    select 
        stuff
        (
            ( 
                    select ' and a ' + o.thing+' priced '+convert(nvarchar(max),val)
                    from owns o
                    where o.firstname=f.firstname
                FOR XML PATH(''), TYPE
            ).value('.','nvarchar(max)')
            ,1,7, ''
        ) as collection_with_value
) as q2

Upvotes: 4

Views: 1252

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

Or even this way, pure XQuery based.

SQL

-- DDL and sample data population, start
DECLARE @owns TABLE (firstname nvarchar(50), thing nvarchar(50), val float);
INSERT  @owns VALUES
('mike','Tesla',50000),
('mike','Mona Lisa',3000000000);
-- DDL and sample data population, end

DECLARE @separator VARCHAR(10) = ' and a ';
WITH rs AS
(
    SELECT firstname
    , (
       SELECT * 
       FROM @owns AS c
       WHERE c.firstname = p.firstname
       FOR XML PATH('r'), TYPE, ROOT('root')
    ) AS xmldata
    FROM @owns AS p
    GROUP BY p.firstname
)
SELECT * 
    , 'My friend ' + firstname + ' owns a ' + xmldata.query('
        for $x in /root/r
        return if ($x is (/root/r[position() = last()])[1]) then string($x/thing[1])
                else concat(($x/thing/text())[1], sql:variable("@separator"))
       ').value('text()[1]', 'VARCHAR(MAX)') AS what_I_say_to_friends
    , 'My friend ' + firstname + ' owns a ' + xmldata.query('
        for $x in /root/r
        let $token := concat(string($x/thing[1]), " priced ", string($x/val[1]))
        return if ($x is (/root/r[position() = last()])[1]) then $token
                else concat($token, sql:variable("@separator"))
       ').value('text()[1]', 'VARCHAR(MAX)') AS what_I_say_to_finance 
FROM rs;

Upvotes: 0

Charlieface
Charlieface

Reputation: 72501

You can't quite do this without an extra subquery, but you can avoid querying the same table again and again.

All you need to do, is to get the data into a single XML blob in one subquery, then query it back out in each of the other subqueries:

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a '+q1.collection_no_value as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a '+q2.collection_with_value as what_I_say_to_finance
from 
    friend f

    cross apply
    (
        select (
            select o.thing, o.price
            from owns o
            where o.firstname = f.firstname
            FOR XML PATH('row'), TYPE
        )
    ) x(XmlBlob)

    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + x2.rw.value('(thing/text())[1]','nvarchar(max)')
                    from x.XmlBlob.nodes('/row') x2(rw)
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7,''
            ) as collection_no_value
    ) as q1
    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + x2.rw.value('(thing/text())[1]','nvarchar(max)') + ' priced ' + x2.rw.value('(price/text())[1]','nvarchar(max)')
                    from x.XmlBlob.nodes('/row') x2(rw)
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7, ''
            ) as collection_with_value
    ) as q2

As you can see, it's actually more long-winded. On the other hand, if the subquery to create the blob is very complex then it may be more performant because the subquery is only executed once.

You can also achieve the same effect by creating a JSON array, in SQL Server 2016

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a '+q1.collection_no_value as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a '+q2.collection_with_value as what_I_say_to_finance
from 
    friend f

    cross apply
    (
        select (
            select o.thing, o.price
            from owns o
            where o.firstname = f.firstname
            FOR JSON PATH
        )
    ) j(JsonBlob)

    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + JSON_VALUE(j2.value, '$.thing')
                    from OPENJSON(j.JsonBlob) j2
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7,''
            ) as collection_no_value
    ) as q1
    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + JSON_VALUE(j2.value, '$.thing') + ' priced ' + JSON_VALUE(j2.value, '$.price')
                    from OPENJSON(j.JsonBlob) j2
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7, ''
            ) as collection_with_value
    ) as q2

Obviously, in SQL Server 2017+ you can just use STRING_AGG:

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a ' + STRING_AGG(CAST(o.thing AS nvarchar(max)), ' and a ') as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a ' + STRING_AGG(o.thing + ' priced ' + convert(nvarchar(max), o.price), ' and a ') as what_I_say_to_finance
from 
    friend f
group by f.firstname

Upvotes: 3

Related Questions