STRING_SPLIT Add Option to Return Row Number
It would be really handy if the STRING_SPLIT function had an option/switch to return the row number in order to be able to use other windowing functions on the resultset.
I found I still had to result to a CTE effectively making my own string splitting function which included the row number to then be able to apply other windowing functions such as a running total.
This code helps to highlight the issue. In trying to apply a running total the results have to be ordered and the position of Susan and Michael is switched. Had a Row Number field existed, ordering by that would have maintained the original order:
DECLARE @str NVARCHAR(MAX) = 'Ben|10, Bob|325, Susan|7, Michael|99'
RunningTotal = SUM ( N.Amount ) OVER ( ORDER BY N.PersonName ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
PersonName = LEFT ( TRIM ( Value ), CHARINDEX ( '|', TRIM ( Value ) ) - 1 ),
Amount = CAST ( RIGHT ( TRIM ( Value ), CHARINDEX ( '|', REVERSE ( TRIM ( Value ) ) ) - 1 ) AS int )
FROM STRING_SPLIT ( @str, ',' )
Upvotes: 7<=-=Apr 18 2017 7:25AM=-=>
Thanks for the suggestion. This is in our backlog but we cannot confirm when it will be implemented.<=-=May 14 2017 8:42AM=-=>
This suggestion is a duplicate of:
L. Szozda commented
I strongly aggree that SQL Server should support this feature(it is available in PostgreSQL UNNEST() WITH ORDINALITY).
As a workaround OPENJSON and KEY could be used:
DECLARE @str NVARCHAR(MAX) = 'Ben|10, Bob|325, Susan|7, Michael|99';
SELECT s.col, PersonName, Amount,rn,
RunningTotal = SUM(Amount) OVER(ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM (SELECT @str) s(col)
CROSS APPLY (SELECT
PersonName = LEFT(TRIM(Value), CHARINDEX('|', TRIM (Value))-1)
,Amount = CAST(RIGHT(TRIM(Value), CHARINDEX('|', REVERSE (TRIM(Value)))-1) AS INT)
,rn = [key]
REPLACE(CONCAT('["',s.col,'"]'), ', ', '","')))
Mark Iannucci commented
If you're looking for the duplicate suggestion, it is here: