want the values of alrecords in a string variable.

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

SQL Server 2012 Forums

Transact-SQL (2012)

want the values of alrecords in a string variable.

Author

Topic

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-26 : 14:49:39

i have a table where i have 10 records( for eg)
if i query:

select * from tablename order by tableid

i get 10 records. what i want is a query which will give me the output of all records in a stringvariable.

for eg: select * from tablename order by tableid if it gives
a1
b2
c3
d4
e5
f6...

i need the output as
a1b2c3d4e5....

can anyonehelp?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-26 : 15:07:29

Not sure why you'd want to do this in T-SQL, but here you go:

declare @t table (c1 varchar(5))

insert into @t values ('a1'),('b2'),('c3'),('d4'),('e5')

declare @s varchar(100)
set @s = ''

select @s = @s + c1
from @t

select @s

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-28 : 18:52:40

thanks that worked.

I want this to get worked in t-sql because stored procs work quicker and i want the results directly sent to the html page, instead of processing in a aspx/asp page.

my question now is @table declaration. - will it slow down or use a lot of memory for processing.?

venkat.

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-28 : 20:37:22

I used this.

Declare @abc nvarchar(max)
declare @SearchKey nvarchar(200),

select top 10 @abc = COALESCE(@abc + '','') + '<li>' + ProductID + '<li>' + ProductName + char(10) + char(13)
from Product where ProductName like '%'+ @SearchKey +'%' or ProductShortDesc like '%'+ @SearchKey +'%'

is it a right way to use?

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-30 : 06:04:33

any answers please?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-30 : 12:36:56

Does it produce what you want? If so, then yes that'll work. We don't have your data to be able to give you an answer. It needs to be tested by you.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-30 : 12:43:29

Note that:


select @s = @s + c1
from @t

is an undocumented use and can produce incorrect results

Better to use:


select @s = (select c1 + ''  from @t for xml path(''))

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-30 : 15:24:53

I will try both thanks.

regarding this one.
select top 10 @abc = COALESCE(@abc + '','') + '<li>' + ProductID + '<li>' + ProductName + char(10) + char(13)
from Product where ProductName like '%'+ @SearchKey +'%' or ProductShortDesc like '%'+ @SearchKey +'%'

order by clause doesnot work. it always orders by the primary key even if i give any other field.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-30 : 15:26:46

quote:
Originally posted by misterraj

I will try both thanks.

regarding this one.
select top 10 @abc = COALESCE(@abc + '','') + '<li>' + ProductID + '<li>' + ProductName + char(10) + char(13)
from Product where ProductName like '%'+ @SearchKey +'%' or ProductShortDesc like '%'+ @SearchKey +'%'

order by clause doesnot work. it always orders by the primary key even if i give any other field.

I don't see an ORDER BY in your query. Please show us the actual query and sample data that shows the issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources