OLAP function for string concatenation - by AKuz

Status : 


Sign in
to vote
ID 427987 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 3/28/2009 5:22:00 PM
Duplicates 125819 289022 Access Restriction Public


I think it would be very useful to have yet another OLAP function  for string concatenation. How about the following explicit syntax:


 OVER(PARTITION BY ZIP ORDER BY CustomerName) AS CustomersList

FROM ...

Sample output:

10104  Aaron Darrett,Bill Zhang

60601  Jill King,Juan Martinez,Zach Nash
Sign in to post a comment.
Posted by Andomar_ on 7/2/2014 at 6:12 AM
I recently got a PostgreSQL database to maintain. Having string_agg is really nice for quick reports.

On StackOverflow people ask for this function multiple times a day, see f.e. http://stackoverflow.com/questions/24531290/mssql-return-single-row-from-one-to-many-tables

Shame this is still missing in SQL Server.
Posted by Naomi N on 10/28/2009 at 4:35 PM
I agree with the last idea and perhaps we need a syntax for not only window function, but just GROUP BY (though how we would specify order when?).

Posted by AKuz on 5/28/2009 at 11:27 AM
I agree with Steve's idea - there should be separate orderings for concatenation and for window definition.
Posted by Steve Kass on 5/28/2009 at 10:52 AM
Hi Jim,

If you consider this, I strongly urge you to put the ORDER BY that applies to the concatenation order in a different place in the syntax. What Alex has suggested will cause problems down the road, and there is perfectly good alternative that will not cause these problems.

This is my suggestion:

CONCATENATE(<Expression>,<Separator>,<Order specification>) OVER (
<window specification>

The <window specification> may or may not contain an <order specification>, and the window's order is an entirely different semantic part of the query from the ordering of CONCATENATE.

If you don't do this, you'll repeat the mistake you made with TOP, where ORDER BY in a query had two meanings - one for presentation order and one to specify result order. This led to lots of problems (UNION bugs, "ordered views"). The situation is identical here, although perhaps hard to recognize because T-SQL does not yet implement full OVER support.

Inside the OVER clause, the ORDER BY should only be used to give meaning to the window specification.

As far as I can see, moving the ORDER BY of an "ordered aggregate" where I suggest (to a parameter of the aggregate) has only advantages and no disadvantages. It's clearer and allows more functionality. In particular, the result of the aggregate can then be grouped and ordered separately:

CONCATENATE(EmployeeName,@comma,ORDER BY EmployeeRegion,EmployeeName)
Partition by CustomerID

Another advantage: no OVER may be needed, and ordered concatentation would work with regular GROUP BY:

CONCATENATE(OrderID,@comma,ORDER BY OrderDate DESC) as OrderList
FROM Orders

This is more complicated to do with the syntax Alex suggests.

Thanks for listening!

Posted by JDS4444 on 4/8/2009 at 2:17 PM
Also adding a simple way to wrap it would be helpful. Many times I need to incorporate quotes (" ") around strings. This would also be useful when building SQL that dynamically creates SQL INSERTS etc.

CONCATENATE(<Expression>, <Separator>, <Text Qualifier>)
Posted by AKuz on 4/1/2009 at 12:53 PM
Hi Jim,

I searched on 12642 and that returned nothing. Can you please verify that the number 12642 is correct.

Posted by Microsoft on 4/1/2009 at 12:32 PM
Hi Alex,

Thankyou for this request. Yes, it's a good idea.

You probably know this already, but there are 3 (painful) workarounds:

1) a lengthy query that using PIVOT
2) a CLR aggregator function

But a builtin string "aggregator" function as you suggest would be so much simpler. (It struck me, as I read, that SQL makes the hard things easy - eg: SELECT, GROUP, ORDER, JOIN - but we forgot to make the easy things easy too!)

We have a request for this already, but I'll add yours to the case. (The other request is #12642 - worth a read - it includes a workaround posted by Erland Sommarskog)


Jim (Hogg)