Home Forums SQL Server 2005 Development Create string using input values RE: Create string using input values

  • January 25, 2013 at 11:16 am

    #1581159

    Sql Student-446896 (1/25/2013)

    Hi,

    I have a beginning Year and a End year and i have to compute/create a string based on the given years.

    Example:

    Input: BegYr = 2013 and EndYr = 2015

    Output: CombYr = 3/4/5

    How do i do this as script?

    Here's one simple kludgy way:

    DECLARE @BegYr INT = 2013, @EndYr INT = 2015

    DECLARE @CombYr VARCHAR(10) = ''

    ;WITH Tally AS (SELECT n = 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)

    SELECT @CombYr = @CombYr + '/'+RIGHT(@BegYr + n,1)

    FROM Tally

    WHERE @BegYr + n BETWEEN @BegYr AND @EndYr

    SELECT STUFF(@CombYr,1,1,'')

    Look up Tally tables and FOR XML PATH to see how thiscan get interesting.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]