如何使用STRING_AGG实现带首尾引号的文本元素拼接？

现有查询语句 #

select  
    (
        SELECT  STRING_AGG(TRIM(innerAgg.Result), '" - "')
        FROM
        (
            SELECT  DISTINCT
                value
            FROM    STRING_SPLIT(STRING_AGG(t1.[Sample Values], '-'), '-')
        ) AS innerAgg(Result)
    ) as [Sample Values]
from 
(
    select
        'abc' as [Sample Values]
    union all
    select
        'def'
    union all
    select
        'ghi'
) t1

当前返回结果 #

abc" - "def" - "ghi

期望返回结果 #

"abc" - "def" - "ghi"

修改方案 #

原查询仅在元素之间添加了带引号的分隔符，但没有给每个元素本身包裹首尾引号。只需调整内层STRING_AGG的逻辑：先给每个innerAgg.Result前后拼接"，再用' - '作为分隔符拼接即可。

修改后的完整查询 #

select  
    (
        SELECT  STRING_AGG('"' + TRIM(innerAgg.Result) + '"', ' - ')
        FROM
        (
            SELECT  DISTINCT
                value
            FROM    STRING_SPLIT(STRING_AGG(t1.[Sample Values], '-'), '-')
        ) AS innerAgg(Result)
    ) as [Sample Values]
from 
(
    select
        'abc' as [Sample Values]
    union all
    select
        'def'
    union all
    select
        'ghi'
) t1

这样每个值都会被双引号包裹，再用-连接，最终就能得到目标格式的结果。

内容的提问来源于stack exchange，提问作者Philip