Well, this is not some thing SQL Server has. This is a basic function you get in ORACLE or in mySQL.
What it does.
Let us look at this data set. It has Color and ProductNumber where ProductNumber is the business unique key,
What I need is following output.
In mySQL you can use simple function GROUP_CONCAT
in Oracle you have LISTAGG function
In both cases it is one table scan which means cost is low,
Let’s move to SQL Server.
You don’t have a out-of-box function. However there is a workaround for this. Honestly I don’t know how this is working.
and the query plan is,
In case of SQL Server, you need to use DISTINCT and WHERE IS NOT NULL and more importantly there is two table scans, which means you might not use this for large tables.
So has Microsoft SQL Server team has reserved this for future versions.