0

I have a query where I am trying to join distinct values from two rows. I am using Azure SQL DW and it does not support XML functions.

This is the query I have written:

SELECT CAST(STUFF((
    SELECT ',' + cast(s.MONTH_ID AS VARCHAR(6))
    FROM (
        SELECT DISTINCT CAST(MONTH_ID AS VARCHAR(6)) AS MONTH_ID
        FROM dbo.MARGIN_CLASS_FACT
        ) s
    FOR XML PATH('')
    ), 1, 1, '') AS VARCHAR(35)) AS MONTH_ID;

When i am using FOR XML PATH I am getting this error ** Msg 103010, Level 16, State 1, Line 1 Parse error at line: 1, column: 212: Incorrect syntax near 'FOR'.**

the input is: 

2018
2019

The result output should be like this: 

201808,201809
Jeremy J.
  • 697
  • 4
  • 9
Looking_for_answers
  • 343
  • 1
  • 6
  • 20
  • The Stuff / FOR XML code is a traditional hack around Sql Server's lack of MySql's GROUP_CONCAT feature. Although Sql Server itself now finally has STRING_AGG, this isn't yet supported on Azure DWH as per the Duplicate link. – StuartLC Oct 01 '18 at 16:15

1 Answers1

1

The below is Azure SQL 12.0.2000.8, but obviously doesn't work on Azure DWH

The Stuff / Xml hack to work around the lack of MySql's GROUP_CONCAT / Oracle's LIST_AGG is no longer needed in recent versions of Sql - we now have STRING_AGG versions 2017 and later:

select STRING_AGG(MyColumn, ',')
from [dbo].[MyTable];

I've just checked this on an recent Azure DB

Microsoft SQL Azure (RTM) - 12.0.2000.8 Aug 31 2018 20:17:59
Copyright (C) 2018 Microsoft Corporation

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • 1
    it is showing a STRING_AGG is not a built in function – Looking_for_answers Oct 01 '18 at 15:59
  • I just ran a query with STRING_AGG against a SQL Azure database, and it worked fine. Microsoft SQL Azure (RTM) - 12.0.2000.8 Aug 31 2018 20:17:59 Copyright (C) 2018 Microsoft Corporation – Jeremy J. Oct 01 '18 at 16:06
  • My Bad - the OP has listed Azure DWH. Likely not updated yet with `STRING_AGG`. – StuartLC Oct 01 '18 at 16:08
  • @StuartLC: How do I proceed as you have marked it as a duplicate – Looking_for_answers Oct 03 '18 at 12:42
  • That's pretty much it - there is no answer, as Azure DW neither supports most of the XML (or JSON) functionality needed to do your STUFF / XML hack, and neither does it support the 'proper' STRING_AGG feature that MS finally implemented in the mainstream SQL (incl Azure) database. There's a feature request for [this here](https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/14876763-stuff-for-xml). You might try [these other workarounds](https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) too. – StuartLC Oct 03 '18 at 12:50