January 17, 2017 at 9:36 am
Hi,
I have the following code below, which is using a tally table, to split out a text string into its component pieces, along with the signs associated with each piece.
I want to get the function to add an extra column which just displays a number representing the number of rows that it produced. So for my example below, it would return a column that has the value of 3. Can anyone suggest how I would write the extra column, to achieve this result? I feel like I should be able to manipulate my tally table somehow to return the result, but I'm not sure how to do that. There are ways where I could take this dataset and do it as another separate step (becomes easy then, as I would just start counting the values, then join this result back onto the original dataset), but is there some kind of efficient way to do it while I am in this step?
DECLARE @pString VARCHAR(8000) = '-[abdsad]+[cd]+[ef]';
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) IN ('+','-') OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT
Items = REPLACE(REPLACE(REPLACE(REPLACE(
SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))
,'[',''),']',''),'(',''),')',''),
Signs = CASE WHEN s.N1-1 = 0 THEN '+' ELSE SUBSTRING(@pString,s.N1-1,1) END
, ItemNumber= ROW_NUMBER() OVER(ORDER BY s.N1)
FROM cteStart s
WHERE
CASE LEFT(@pString,1)
WHEN '-' THEN s.N1-1
WHEN '+' THEN s.N1-1
ELSE 1 END <> 0
;
January 17, 2017 at 10:12 am
Just add
, cnt = COUNT(*) OVER()
You need the OVER clause, because there is no group by, but you don't have to have any partition or order by clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2017 at 2:15 am
Thanks, for some reason, it never occurred to me that window functions could be used for anything other than ranking, which is how I normally use it. Opens up a whole new world of possibilities 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply