Sunday, February 12, 2012

GROUP_CONCAT or LISTAGG

 

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,

image

What I need is following output.

image

In  mySQL you can use simple function GROUP_CONCAT

image

in Oracle you have LISTAGG function

image

Source :http://oracleabc.com/b/archives/2100

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.

image

and the query plan is,

image

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.

No comments:

Post a Comment