Home Dashboard Directory Help
Search

OLAP function for string concatenation by AKuz


Status: 

Active


64
1
Sign in
to vote
Type: Suggestion
ID: 427987
Opened: 3/28/2009 5:22:00 PM
Access Restriction: Public
Duplicates: 125819 289022
0
Workaround(s)
view

Description

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

SELECT ZIP, CONCATENATE(CustomerName, ',')

OVER(PARTITION BY ZIP ORDER BY CustomerName) AS CustomersList

FROM ...

Sample output:

10104 Aaron Darrett,Bill Zhang

60601 Jill King,Juan Martinez,Zach Nash
Details
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)
OVER (
Partition by CustomerID
ORDER BY OrderDate DESC
10 ROWS PRECEDING
)

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

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

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

Thanks for listening!

SK
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.

Thanks,
AK
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
3) FOR XML

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)

Thanks,

Jim (Hogg)
Sign in to post a workaround.