从Azure SQL Dw中的不同行连接列的值 [英] Concatenating a column's value from different rows in Azure SQL Dw

问题描述

HI，

我需要将所有部门名称连接成一个字符串。这是拥有PIVOT但具有动态内置部门名称的更大要求的一部分。 （请参见此处：

https://stackoverflow.com/questions/42292480/dynamic-pivot-query-how-to-save-it-in-sql-server

请注意，新的部门可以随时添加到表格中。 

为了将名称变为字符串，我有以下内容：

  ;

DECLARE @ColumnName AS VARCHAR（1000）



选择@ ColumnName = COALESCE（Rtrim（@ColumnName +'，['+ [姓名] +']'），'['+ [姓名] +']'）

         FROM   （从[dbo]中选择DISTINCT [名称]。[Depttable]）部门

选择@ColumnName

这在标准SQL Server中运行良好但在DW上我得到以下错误：

Msg 104473，Level 16，State 1，Line 115

已变量在SELECT语句中分配不能包含d在与from子句一起使用时表达式或赋值。

如何在Azure SQL DW中实现此目的？

谢谢

MS

解决方案

<你有解决方案吗？我正在寻找同样的东西。

Hrushi

HI,

I need to concatenate all the department names in to a string. This is as part of  a larger requirement to have a PIVOT but with dynamic built department names. (please see here :

https://stackoverflow.com/questions/42292480/dynamic-pivot-query-how-to-save-it-in-sql-server

Please note new departments could be added into the table all the time. 

For for getting the names into a string, I have the following :

 

DECLARE @ColumnName AS VARCHAR(1000)

Select  @ColumnName=COALESCE (Rtrim(@ColumnName + ',[' + [Names] + ']'), '['+[Names]+']')
               FROM    (SELECT DISTINCT [Names] from [dbo].[Depttable]) Department
select @ColumnName

This works well in standard SQL Server but on DW i get the following error:

Msg 104473, Level 16, State 1, Line 115
A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause.

How can i achieve this in Azure SQL DW?

Thanks

MS

解决方案

Hi,

did you get any solution to this? I am exactly looking for the same.

Hrushi

这篇关于从Azure SQL Dw中的不同行连接列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！