如何在Microsoft SQL Azure DWH中复制GROUP_CONCAT函数? [英] How to replicate GROUP_CONCAT function in Microsoft SQL Azure DWH?

问题描述

MYSQL中有一个功能GROUP_CONCAT，它可以用逗号连接几行并将结果合并为一行.谁能说出如何在Microsoft SQL Azure DWH中复制相同的功能?

There is a function GROUP_CONCAT in MYSQL which can concatenate several rows with comma and merge the result into a single row. Can anyone say how to replicate the same functionality in Microsoft SQL Azure DWH?

推荐答案

您好，

Hello,

您可能找不到一种直接的方法，但是以下T-SQL代码可能对您有用:

You may not find a direct way to do it but the following T-SQL code may work for you:

DECLARE @intColumnCount   INT，

DECLARE @intColumnCount  INT,

  ;      @intProcessCount INT，

        @intProcessCount INT,

  ;       @varColList         VARCHAR(max)

        @varColList      VARCHAR(max)

SET @varColList =''

SET @varColList = ''

IF Object_id('tempdb.dbo.#tempColumnNames')不为空

IF Object_id('tempdb.dbo.#tempColumnNames') IS NOT NULL

  ; 开始

  BEGIN

  ;    DROP TABLE #tempcolumnnames;

      DROP TABLE #tempcolumnnames;

  ; END

  END

创建表#tempcolumnnames

CREATE TABLE #tempcolumnnames

  ; (

  (

  ;    intid          INT，

     intid          INT,

  ;    varcolumnnames VARCHAR(256)

     varcolumnnames VARCHAR(256)

  ; )

  )

插入#tempcolumnnames

INSERT INTO #tempcolumnnames

SELECT Row_number()

SELECT Row_number()

  ;       OVER(

         OVER (

  ;        按名称订购)，

           ORDER BY NAME),

  ;     名称

       NAME

FROM    sys.tables

FROM   sys.tables

SET @intProcessCount = 1

SET @intProcessCount = 1

SET @intColumnCount = (选择计数(*)

SET @intColumnCount = (SELECT Count(*)

  ;   FROM   #tempcolumnnames)

                       FROM   #tempcolumnnames)

WHILE(@intProcessCount< = @intColumnCount)

WHILE ( @intProcessCount <= @intColumnCount )

  ; 开始

  BEGIN

  ;    SET @varColList = @varColList +'，'

      SET @varColList = @varColList + ', '

  ;             +(选择varcolumn名称

                        + (SELECT varcolumnnames

  ;              nbsp; b ; FROM   #tempcolumnnames

                           FROM   #tempcolumnnames

  ;              nbsp;   在哪里  intid = @intProcessCount)

                           WHERE  intid = @intProcessCount)

  ;    SET @intProcessCount + = 1

      SET @intProcessCount +=1

  ; END

  END

SELECT资料(@varColList，1，2，'')

SELECT Stuff(@varColList, 1, 2, '')

希望这会有所帮助.

Hope this helps.

问候

Regards,

Alberto Morillo
SQLCoffee.com

Alberto Morillo
SQLCoffee.com

这篇关于如何在Microsoft SQL Azure DWH中复制GROUP_CONCAT函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！