Synapse Serverless - Other than String_agg,for Xml to convert row to column

Question

I'm using Synapse Serverless and I want to convert row to Column. Use STRING_AGG but due to nvarchar(8000) limitation I was getting error "STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation" due to that I tried to rtecreate the Query with XML path and Stuff but Serverless wont support. Is there any workaround?

Answer 1

The error STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation has a workaround. The STRING_AGG has a limit of 8000 bytes by default, but when it exceeds this limit, you can change the limit to nvarchar(max) or varchar(max) using CONVERT inside STRING_AGG.

Refer to the following link to know how to do the above conversion and understand more information about STRING_AGG with CONVERT.

https://www.mssqltips.com/sqlservertutorial/9371/sql-string-agg-function/

There is a relational operator called PIVOT which conventionally helps to transform rows data into columns (UNPIVOT operator is also available- does the exact opposite of what PIVOT does). The following is a syntax of PIVOT:

SELECT (ColumnNames) 
FROM (TableName) 
PIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn IN (PivotColumnValues)
 ) AS (Alias)

Refer to the following link to understand completely about PIVOT and refer to the second link and check if any provided method can help you achieve the requirement:

• https://www.appsloveworld.com/sql-server-simple-way-to-transpose-rows-into-columns/
• Efficiently convert rows to columns in sql server