1

i'm using the string_agg function in some query bu trying to convert it into azure sql data warehouse.How could i achieve this? Can any one help me on this issue?

pythonUser
  • 183
  • 2
  • 7
  • 20
  • Possible duplicate of [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) – Thom A Apr 05 '19 at 14:45

2 Answers2

2

As of the July release of Azure Data Warehouse, STRING_AGG is implemented.

https://learn.microsoft.com/en-us/azure/sql-data-warehouse/release-notes-10-0-10106-0

Ward
  • 39
  • 7
0

string_agg not supported in azure data ware house. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017. you can use https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017.

By using: COALESCE

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + ColumnName FROM TABLE_NAME Select @Names

Hasan Mahmood
  • 978
  • 7
  • 10
  • How does `COALESCE` create a delimited list? `COALESCE` is a short hand `CASE` expression. – Thom A Apr 05 '19 at 14:46
  • @Larnu see this: (https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Hasan Mahmood Apr 05 '19 at 14:54
  • My point was to explain to the OP, Hasan. This does work fine, until the OP needs this per row though. – Thom A Apr 05 '19 at 14:59
  • Thanks for the reply @hasan Mahmood and Larnu.In case if i have multiple rows then how to get those as comma seperated values.Can you please help me on this? Thanks in advance – pythonUser Apr 05 '19 at 15:57
  • @Hasan Mahmood .I have tried the code which you have mentioned earlier but i'm getting the following error(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.) Declare atNumbers AS Nvarchar(MAX) SELECT atNumbers = COALESCE(atNumbers + ',', '') + A FROM [dbo].[TEST1] SELECT atNumbers Note:at=@ – pythonUser Apr 05 '19 at 16:40
  • @pythonUser try this: `Declare @atNumbers Nvarchar(MAX) SELECT @atNumbers = COALESCE(@atNumbers + ',', '') + A FROM [dbo].[TEST1] SELECT @atNumbers` – Hasan Mahmood Apr 05 '19 at 17:26
  • Thanks Hasan Mahmood for your reply . I have tried the above query which you mentioned in the comment but i'm still getting the same error as i mentioned in the earlier comment. Can you please provide any alternate solution for this? – pythonUser Apr 08 '19 at 11:03
  • below is the code i have used Declare atNumbers Nvarchar(MAX) SELECT atNumbers = COALESCE(atNumbers + ',', '') + A FROM [dbo].[TEST1] SELECT atNumbers note:@should be added wherever it needs – pythonUser Apr 08 '19 at 13:46
  • @HasanMahmood: Below is the error Msg 104473, Level 16, State 1, Line 28 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. – pythonUser Apr 08 '19 at 13:54
  • @pythonUser I need your whole code and TEST1 table structure with some sample data, BTW there are some other ways to do that as well. – Hasan Mahmood Apr 08 '19 at 13:55
  • @Hasan Mahmood: above one is my whole code.i want to achieve the data in comma separated values.My table structure and as follows: Test1 table:A nvarchar(200),B nvarchar(200) column 1data:A abc dfg efh – pythonUser Apr 08 '19 at 14:02
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191460/discussion-between-hasan-mahmood-and-pythonuser). – Hasan Mahmood Apr 08 '19 at 14:33