-1

How to convert FOR XML PATH to string_agg?

Stuff((SELECT '' + @delimiter + '' + [Column]  
       FROM CTE GROUP BY [column]
      FOR XML PATH('') ),1,1,'')

I was trying like

STRING_AGG('', @delimiter, '', [Column] )   FROM CTE  GROUP BY [Column]

But not getting the expected result. Thanks in advance.

James Z
  • 12,209
  • 10
  • 24
  • 44
moor
  • 45
  • 6
  • Please provide some sample data and the expected result as tables in your question, see https://stackoverflow.com/help/minimal-reproducible-example At first glance, it seems incorrect to me to group by the same column which is used in STRING_AGG, but your question is too vague to be sure about the correct query. – Jonas Metzler Jan 24 '23 at 12:14
  • FYI, `STUFF` isn't doing the work here, it's `FOR XML PATH`. All `STUFF` is doing it removing the first character from a string. – Thom A Jan 24 '23 at 12:28
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Jan 24 '23 at 15:41

1 Answers1

2

You are trying this:

STRING_AGG('', @delimiter, '', [Column] )   FROM CTE  GROUP BY [Column]

which means you want to concatenate the values in the column, but grouping by the same values. So, you will get only one value.

From here, first remove the GROUP BY and use the correct syntax:

STRING_AGG([Column],@delimiter) FROM CTE

Also, pay attention to this section from the above linked documentation:

Return Types

Return type depends on first argument (expression). If input argument is string type (NVARCHAR, VARCHAR), result type will be same as input type. The following table lists automatic conversions:

Input expression type Result
NVARCHAR(MAX) NVARCHAR(MAX)
VARCHAR(MAX) VARCHAR(MAX)
NVARCHAR(1...4000) NVARCHAR(4000)
VARCHAR(1...8000) VARCHAR(8000)
int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime, datetime2, NVARCHAR(4000)

If you are expecting the concatenated string to exceed VARCHAR(8000) or NVARCHAR(4000), you need to cast the value like this:

STRING_AGG(CAST([Column] AS VARCHAR(MAX)),@delimiter) FROM CTE
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 2
    If you expect the value to *exceed* 8,000/4,000 characters, you need to `CAST`/`CONVERT` to a `MAX`. `CAST`ing to a `varchar(8000)` will result in a truncation error if the value results in more than than many characters, as (as the documentation you quote states) providing a non-`MAX` value results in a non-`MAX` length value being returned. – Thom A Jan 24 '23 at 12:52
  • @Larnu Thank you for improving the quality, also for noting the error. – gotqn Jan 24 '23 at 12:53
  • @gotqn, thanks for guiding. Yes, my string concatenation is works. But i want to remove the duplicate string. Eg (abc, def, abc), the output should be like (abc, def) – moor Jan 24 '23 at 12:56
  • 1
    @moor in order to remove the duplicates, you need first to perform DISTINCT. Check this answer - https://stackoverflow.com/q/50589064/1080354 – gotqn Jan 24 '23 at 12:57