Concate with a loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concate with a loop

Author

Topic

randomizer
Starting Member

8 Posts

Posted - 2014-04-05 : 02:21:35

Hi,

I'm new to SQL and I've been trying this for a while now.

I have let's say a loop of numbers from one to ten. It appears like this:

1
2
3
4
5

What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.

Any chance anyone can help me out on this one?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-05 : 04:11:49

[code]declare @strvar varchar(100)

select @strval = isnull(@strval, '') + convert(varchar(10), number)
from sometable
order by number
[/code]

KH
[spoiler]Time is always against us[/spoiler]

randomizer
Starting Member

8 Posts

Posted - 2014-04-05 : 05:50:46

Thanks for the quick response!

I see that you use a table in your response but I've been testing to see if I can use one without using a table.

Here is my loop query

Declare @number1 int
Set @number1=0
While (@number<0)
Begin
Set @test+=1
Select @test
End

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-05 : 06:14:22

sorry, don't quite understand that. You just want to generate a string '12345' ?

KH
[spoiler]Time is always against us[/spoiler]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-04-05 : 07:09:57

[code]WITH CTE(n) AS ( --declare common table expression
SELECT 1 AS n --start n at 1
UNION ALL
SELECT n+1 --increment n
FROM CTE
WHERE n<10) --until n reaches 10
SELECT ''+cast(n as varchar) --convert number to character, concat with empty string
FROM CTE
ORDER BY n
FOR XML PATH('') --use FOR XML with empty PATH to generate concatenated string[/code]

randomizer
Starting Member

8 Posts

Posted - 2014-04-05 : 07:57:16

quote:
Originally posted by khtan

sorry, don't quite understand that. You just want to generate a string '12345' ?

KH
[spoiler]Time is always against us[/spoiler]

I'm sorry if you cannot understand my inquiry. I'm going to try my best to explain it better

No, in the query that I have posted, if you run it will display as:
1
2
3
4
5

What I want to do is to make it appear in a column like this by using concat like this: 12345 but unfortunately I'm having trouble doing so.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-05 : 08:44:32

[code]Declare @number int,
@result varchar(10)
Set @number = 0
While (@number < 5)
Begin
Set @number+= 1
Select @result = isnull(@result, '') + convert(varchar(5), @number)
End

select @result[/code]

KH
[spoiler]Time is always against us[/spoiler]

randomizer
Starting Member

8 Posts

Posted - 2014-04-05 : 10:27:34

quote:

Originally posted by khtan

Declare @number int,
	@result	varchar(10)
Set 	@number = 0
While (@number < 5)
Begin
	Set @number+= 1
	Select @result	= isnull(@result, '') + convert(varchar(5), @number)
End

select	@result

KH
[spoiler]Time is always against us[/spoiler]

Awesome! Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources