0

I did some searching here on this.. but it was either for PHP, Python or didnt relate.

I understand this is somewhat of an odd use for a query..

I have an existing app.

As a parameter to a section in this app (which is for name value pairs).. I can either pass in a string, or a query.

I'd rather not try use a hardcoded NVP 'string' to build this list.. but instead use a query to do this.

It wont be using any table or real data.. just used to build a NVP list of years actually.

What I'm hoping to get help with is to build a string output of years from 1920-2018* (*current year..so this doesnt need to be manually updated each year)..

I'd also like to have/inject a unique entry at the end. Current or Present.

So in the end I get an output like so:

1920|1920|1921|1921|1922|1922.........2018|2018|Present|Present

Is this possibly using a query?

ccarpenter32
  • 1,058
  • 2
  • 9
  • 17
whispers
  • 962
  • 1
  • 22
  • 48

2 Answers2

1

How's this?

declare @string varchar(max)=''
declare @iterator int =1
declare @year int=1920
while @year<=year(getdate())
begin
set @string=@string+'|'+cast(@year as varchar(4))+'|'+cast(@year as varchar(4))
set @year=@year+1
end
select @string+'|Present|Present'
Daniel Marcus
  • 2,686
  • 1
  • 7
  • 13
  • Thanks! Look easier to read/follow (still walking through it somewhat) How can I add in my custom entry at the end here? Like so: (take example from above) select 'at' string = 'at' string + 'Present|Present' – whispers May 02 '18 at 21:59
  • At the end just select @string+'|Present|Present' – Daniel Marcus May 02 '18 at 22:00
  • What's happening here is we are initializing a variable to '1920' and then looping intervals of 1 one at a time until we reach the current year. After each pass in the loop we add on the year of the pass and the bars to the value we have in the variable. As the loop progresses, we continue to build the variable year by year until all the years up until now are accounted for. – Daniel Marcus May 02 '18 at 22:04
  • A couple suggestions: make @year a varchar so you don't have to cast it, and reorder the | bars (right now the output string with start with a |) – Aaron Dietz May 02 '18 at 23:47
0

You could use a loop to build the string:

DECLARE @YearString varchar(MAX) = '', @Year varchar(4) = 1920, @YearStringFinal varchar(MAX)

WHILE @Year <= YEAR(GETDATE())
BEGIN
SET @YearString = @YearString+@Year+'|'+@Year+'|'
SET @Year = @Year +1
END

SET @YearStringFinal = @YearString + 'Present|Present'

SELECT @YearStringFinal

I imagine you'll want to use a procedure for this though:

CREATE PROCEDURE GetYearString @YearString varchar(MAX) = '', @Year varchar(4) = 1920, @YearStringFinal varchar(MAX) OUTPUT
AS
BEGIN

WHILE @Year <= YEAR(GETDATE())
BEGIN
SET @YearString = @YearString+@Year+'|'+@Year+'|'
SET @Year = @Year +1
END

SET @YearStringFinal = @YearString + 'Present|Present'

END

Then output the string and use it as your parameter:

DECLARE @String varchar(MAX)
EXEC GetYearString @YearStringFinal = @String OUTPUT

SELECT @String
Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • Thanks for the reply..although bit over my head. I dont believe I want a procedure? (this needs to be called from within the app) But thanks for the quick reply.. – whispers May 02 '18 at 21:57
  • @whispers What's your app coded in? I suggest the procedure because doing something like this in SQL is usually better kept in a procedure in the database. You would call that procedure from your app, the database would execute the procedure, then return the string to your app (see the last code block of my answer, but it would be translated into your app code). The top code block is the direct SQL to create the string. – Aaron Dietz May 02 '18 at 23:45