Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Convert String to a Table using CTE Expand / Collapse
Author
Message
Posted Monday, December 14, 2009 3:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 8:06 AM
Points: 95, Visits: 368
Thanks for the good article, Amit. I was looking for a way this morning to find all the dates between start date and end date, and there you go, your article is here. I manage to replicate your script to find all the middle dates. Thanks..

and thanks for other contributors for the 'MAXRECURSION' option hints..


ALTER FUNCTION [dbo].[MiddleDatesToTable]
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS
@listTable TABLE
(
item DATETIME
)
AS
BEGIN

;WITH rep (item, nextday) AS
(
SELECT DATEADD(DD,1,@StartDate) as 'item', DATEADD(DD,2,@StartDate) as 'nextday'
WHERE DATEDIFF(DD, DATEADD(DD,1,@StartDate), @EndDate) > 0

UNION ALL

SELECT nextday as 'item', DATEADD(DD,1,nextday) as 'nextday'
FROM rep
WHERE DATEDIFF(dd,nextday, @EndDate) > 0
)
INSERT INTO @listTable
SELECT item FROM rep option (MAXRECURSION 0)

RETURN
END
Post #833693
Posted Monday, December 14, 2009 4:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, Visits: 428
Always good to see different ways of approaching things. Will have to have a look at the issue with the single item here and then maybe set up some performance tests with the examples given.

I also have been using the tally table version.
CREATE FUNCTION dbo.ProcessStringArray (@inputString NVARCHAR(max), @separator NCHAR(1))
RETURNS @output TABLE(arrayItem NVARCHAR(4000))
AS
BEGIN
/**
* Add start and end separators to the Parameter so we can handle single elements
**/
SET @inputString = @separator + @inputString + @separator
INSERT @output
/**
* Join the Tally table to the string at the character level and when we find a separator
* insert what's between that separator and the next one
**/
SELECT SUBSTRING(@inputString,N+1,CHARINDEX(@separator,@inputString,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@inputString)
AND SUBSTRING(@inputString,N,1) = @separator
RETURN
END

Where N is the integer column of the tally table.

Pretty certain I found this based on another article here somewhere but cannot find it to give credit.


_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #833699
Posted Monday, December 14, 2009 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 9, 2011 12:33 AM
Points: 2, Visits: 16
I've used a function similar to MiddleDatestoTable in my apps, however I followed a different approach.

Create Function dbo.udf_NumbersTable (
@riStartingNumber Int, @riCount Int
)
Returns @tbl Table (SequenceNumber Int)
As
Begin
-- Logic used from http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/

;With
tblLevel0 As (Select 1 As C Union All Select 1), --2 rows
tblLevel1 As (Select 1 As C From tblLevel0 As A, tblLevel0 As B),--4 rows
tblLevel2 As (Select 1 As C From tblLevel1 As A, tblLevel1 As B),--16 rows
tblLevel3 As (Select 1 As C From tblLevel2 As A, tblLevel2 As B),--256 rows
tblLevel4 As (Select 1 As C From tblLevel3 As A, tblLevel3 As B),--65536 rows
tblLevel5 As (Select 1 As C From tblLevel4 As A, tblLevel4 As B),--4294967296 rows
tblSeq As (Select Row_Number() Over(Order By C) As N From tblLevel5)

Insert Into @tbl( SequenceNumber )
Select N + @riStartingNumber - 1 From tblSeq Where N Between 1 AND @riCount

Return
End
Go

Declare @dtStart DateTime, @dtEnd DateTime
Declare @iDays Int

-- Example values
Set @dtStart = '20091201'
Set @dtEnd = '20091225'

-- Usage
Set @iDays = DateDiff(d, @dtStart, @dtEnd) + 1

Select DateAdd(d, SequenceNumber, @dtStart) As DateValue
From dbo.udf_NumbersTable(0, @iDays)
Post #833707
Posted Monday, December 14, 2009 5:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:32 AM
Points: 61, Visits: 171
ok .. replace the dates with variables and create a proc

WITH mycte AS (SELECT cast ('2010-01-01' AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < cast ('2011-01-01' AS DATETIME))
SELECT
year(datevalue) as [Year],
month(datevalue) as [Month],
day(datevalue) as [Day],
datename(dw, DateValue) Weekday
from mycte
OPTION (MAXRECURSION 0)
Post #833727
Posted Monday, December 14, 2009 5:57 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 9,928, Visits: 11,206
Liked the article thanks.

But, uh-oh, here we go again with the string-splitting debate...

Like it or not, the overall best method ever is to use a CLR TVF. It just is.

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #833757
Posted Monday, December 14, 2009 6:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 7:31 AM
Points: 155, Visits: 386
Why should I use this method versus using a good old fashioned Numbers table?
Post #833785
Posted Monday, December 14, 2009 6:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 20,860, Visits: 32,884
msaleem-583379 (12/14/2009)
I've used a function similar to MiddleDatestoTable in my apps, however I followed a different approach.

...


You do realize since your routine is a multi-statement TVF that the Quesry Optimizer will always treat the table returned y this function as if it has only one row regardless of the nuumber of rows actually returned?

This means if your function is returning more than a few hundred rows you won't be getting an optimal execution plan.

I discovered this with some AsOF multi-statement TVF's I developed. I had a query linking seven of this TVF together, and after 30 minutes, cancelled the query and modified (actually drop and create) the TVF from multi-statement to in-line TVF. Amazingly, the query then returned in four minutes.

I have to agree with Paul White, here we go with the discussions regarding what is the best string splitting functions. The use of a recursive CTE, however, is not very scalable. If you don't have a CLR TVF, which Paul indicates is the best, the next best is one based on a tally table. I have a function that actually contains its own tally table should you not have one.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #833789
Posted Monday, December 14, 2009 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 5, 2013 2:10 PM
Points: 183, Visits: 529
Rob Fisk (12/14/2009)
Pretty certain I found this based on another article here somewhere but cannot find it to give credit.



I believe that must be a Jeff Moden article, unless I'm mistaken?
Post #833807
Posted Monday, December 14, 2009 7:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
I'd like to do a poll and find out what most people call it - a "tally" table, a "numbers" table, a "helper" table or what. It would be interesting to know.

Maybe there's a geographic trend. a "tally" table sounds like something out of Great Britain - where they ride around on the trolley - while "helper" comes from Texas/Colorado/Tennessee with some cities rated as the friendliest in the U.S. and then maybe straight up "numbers," an ambiguous and somewhat unhelpful name for L.A. and New York City where the people are rated by some as the most unhelpful in the U.S. (Of course we all know these are generalizations that don't hold true for everybody - and where do they come up with these stats anyway? ).

Are there any more names you've heard for the "tally" table?


Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Post #833823
Posted Monday, December 14, 2009 7:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 31, 2014 10:37 PM
Points: 85, Visits: 625
I called mine the 'Integers' table. Because it's a table of INTs. Also, I used to write code for the lumber industry, where 'Tally' has a specific meaning.
Post #833837
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse