CODEWITHSUNDEEP
snowflake-cloud-data-platform
liz
liz

Reputation: 1

Concatenate text from multiple rows into a single text string in SQL Server

The script shown here work in SQL Server but NOT in SNOWFLAKE SQL. What is the equivalent in SNOWFLAKE SQL?

SELECT DISTINCT 
    ST2.SubjectID, 
    SUBSTRING((SELECT ',' + ST1.StudentName AS [text()]
               FROM dbo.Students ST1
               WHERE ST1.SubjectID = ST2.SubjectID
               ORDER BY ST1.SubjectID
               FOR XML PATH (''), TYPE).value('text()[1]', 'nvarchar(max)'), 2, 1000) [Students]
FROM 
    dbo.Students ST2

RESULTS FROM SAMPLE BELOW: IT CONCATENATES TEXT FROM ALL THE ROWS INTO A SINGLE TEXT STRING BY ID

I tried the above in SQL Server and it worked, however, I need to use a datawarehouse in Snowflake and snowflake doesn't use XML PATH. They have XMLGET but I can't figure out how to use it.

Upvotes: 0

Views: 2563

Answers (3)

Paul Vernon
Paul Vernon

Reputation: 3901

As Lukasz mentions, the FOR XML PATH ('') syntax in SQL Server was a common way to implement string aggregation before the existence of an explicate## Heading ## operator in later SQL Server versions. This answer describes how it works in SQL Server.

If you are on a version of SQL Server that support the operator, then you could change your code to use STRING_AGG and test that it gives the correct results on SQL Server. Then to migrate to Snowflake, you can simply change the STRING_AGG keyword to LISTAGG.

If you have a lot of such SQL to convert, you might consider using tooling that will recognize such specialized syntax and convert it to the simpler form.

Upvotes: 1

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25928

so if your source data look like:

    select * from values
    (1, 'student_a'),
    (1, 'student_b'),
    (1, 'student_c'),
    (2, 'student_z'),
    (2, 'student_a')

both the old code and Phil's code will have a random order, the original code did an ORDER BY SubjectID but that is the value being grouped.

In snowflake an order like this can be done with within group (order by studentname)

so Phil's answer becomes:

select 
    subjectid, 
    listagg(distinct studentname,',') within group (order by studentname) as students
from students
group by subjectid;

which then gives the results:

SUBJECTID STUDENTS
2 student_a,student_z
1 student_a,student_b,student_c

Upvotes: 0

Rajat
Rajat

Reputation: 5803

You seem to want listagg. Implementation should look like this

select SubjectId, listagg(distinct StudentName,',') as Students
from your_table
group by SubjectId;

Upvotes: 2

Related Questions