将mysql的group_concat转换为sql查询

问题描述

您好我在MYSQL中有一个使用group_concat的查询。我想把它转换成Sql server查询。

你能帮我吗。



提前致谢下面是查询。



选择apj.package_id，

group_concat（DISTINCT concat（asset.asset_id，' - '，asset.name ）SEPARATOR'||'）'asset_names'

来自stg.asset_package_join apj（NOLOCK），stg.asset asset（NOLOCK）其中asset.id = apj.asset_id

group by apj.package_id



我的尝试：



你好我在MYSQL中有一个使用group_concat的查询。我想将其转换为Sql server查询。

Hi I have a query in MYSQL which is using group_concat. I want to convert it into Sql server query.
Could you please help me.

Thanks in advance. below is the query.

select apj.package_id,
group_concat(DISTINCT concat(asset.asset_id, '-', asset.name) SEPARATOR '||')'asset_names'
from stg.asset_package_join apj (NOLOCK) , stg.asset asset (NOLOCK) where asset.id = apj.asset_id
group by apj.package_id

What I have tried:

Hi I have a query in MYSQL which is using group_concat. I want to convert it into Sql server query.

推荐答案

SELECT
    apj.package_id,
    STUFF
    (
        (
            SELECT DISTINCT '||' + asset.asset_id + '-' + asset.name
            FROM stg.asset As asset
            WHERE asset.id = apj.asset_id
            ORDER BY '||' + asset.asset_id + '-' + asset.name
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'),

        -- Remove the first two characters, which are the extra "||" separator:
        1, 2, ''
    ) As asset_names
FROM
    asset_package_join As apj
;

这篇关于将mysql的group_concat转换为sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！