将mysql的group_concat转换为sql查询 [英] Convert group_concat of mysql to sql query

问题描述

您好我在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.

推荐答案

根据文档 [ ^ ]， GROUP_CONCAT 连接多行的字符串值。



SQL Server中没有直接的等价物，但有几种方法可以重现这种行为：

在Transact-SQL中连接行值：简单对话 [ ^ ]



以blackbox XML方法为例，这样的事情应该有效：

According to the documentation[^], GROUP_CONCAT concatenates string values from multiple rows.

There isn't a direct equivalent in SQL Server, but there are several ways to reproduce the behaviour:
Concatenating Row Values in Transact-SQL : Simple Talk[^]

Taking the "blackbox XML" method as an example, something like this should work:

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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！