Displaying the table rows in a sentance

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 2005 Forums

Transact-SQL (2005)

Displaying the table rows in a sentance

Author

Topic

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-15 : 20:53:24

Hi,
I have a table that consists of 6 or more rows
in this format:
Mr
Antony
joseph
X
Pereira

I nead it to be displayed dynamically in a sentance
Eg: Mr Antony Joiseph X Pereira

Thanks to all

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-15 : 21:40:54

[code]
declare @sentance varchar(max)

select @sentance = isnull(@sentance + ' ', '') + col
from yourtable

select @sentance
[/code]

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

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-16 : 20:17:23

Thanks Khtan, i tried it but it does not give the result i need.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-17 : 01:08:24

is there a column in your table that you can used to determine the sequence ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 10:31:13

quote:
Originally posted by glendcruz

Thanks Khtan, i tried it but it does not give the result i need.

why? whats the issue? can you elaborate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-17 : 20:13:40

declare @temp table(a varchar(20))
insert into @temp values('Mr')
insert into @temp values('Antony')
insert into @temp values('joseph')
insert into @temp values('X')
insert into @temp values('Pereira')
select * from @temp

select (select a as 'data()' from @temp for xml path('')) as WhateverName

--- result
WhateverName
Mr Antony joseph X Pereira

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-17 : 20:23:42

quote:
Originally posted by khtan

is there a column in your table that you can used to determine the sequence ?

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

It is a variable of type table and has an id no created dynamically and can have more than 5:
Eg
1 Mr
2 Joseph
3 Antony
4 X
5 Pereira

I need to display it as a string
thanks once again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-17 : 23:04:03

[code]
declare @table table
(
ID int,
col varchar(10)
)
insert into @table
select 1, 'Mr' union all
select 2, 'Joseph' union all
select 3, 'Antony' union all
select 4, 'X' union all
select 5, 'Pereira'

declare @sentance varchar(max)

select @sentance = isnull(@sentance + ' ', '') + col
from @table
order by ID

select @sentance
/*
Mr Joseph Antony X Pereira
*/
[/code]

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

manub22
Starting Member

6 Posts

Posted - 2010-08-18 : 04:16:23

Lets say the columns are Id & Val in myTable:

So the query becomes:
SELECT STUFF((SELECT ' ' + Val
FROM myTable m
FOR XML PATH('')),1,1,'')

...will provide you a single string separated by single white space.

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-19 : 20:01:47

quote:
Originally posted by manub22

Lets say the columns are Id & Val in myTable:

So the query becomes:
SELECT STUFF((SELECT ' ' + Val
FROM myTable m
FOR XML PATH('')),1,1,'')

...will provide you a single string separated by single white space.

Thanks everyone who helped out i will try it out and get back to all of you

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-21 : 01:26:35

I had tried out all the exmples given and they all work perfectly.
Once again thanks for your help you all are geniouses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources