Snowflake schema on List

17-01-2019  0 Comment(s)

If you want to group by another column, you can add the WITHIN GROUP clause. For example, return a list of concatenated employee names per department.

Since SQL Server 2017, you have the sting_agg function, which has almost the exact same syntax as its Snowflake counterpart. There are two minor differences:

  • Snowflake has an optional DISTINCT
  • SQL Server has a default ascending sorting. If you want another sorting, you can specify one in the WITHIN GROUP clause. In Snowflake, there is no guaranteed sorting unless you specify it (again in the WITHIN GROUP clause).

If you are working on a version of SQL Server before 2017, you’ll appreciate the simplicity of the LISTAGG/STRINGAGG function, since you have to resort to some hacks to get the job done. My favorite article which lists a lot of potential solutions is concatenating things in SQL. My favorite method is the “black-box XML” method (I still had to look up the syntax every time). It’s ugly, but quite fast. The STUFF function is used to remove the trailing comma.

Comment Here


No Comments to Show