Prev: Optimizing queries with isnull
Next: combine 2 selects in where clause
From: Andy B. on 30 Mar 2010 03:53
I need to take the table variable @NonexistingHeadlines and build a string
out of all of the values in the column HeadlineTitle. Is there a way to do
it?



From: Tom on 30 Mar 2010 08:18
On Mar 30, 3:53 am, "Andy B." <a_bo...(a)sbcglobal.net> wrote:
> I need to take the table variable @NonexistingHeadlines and build a string
> out of all of the values in the column HeadlineTitle. Is there a way to do
> it?

Yes

select Column1 + Column2 + Column3

You will have to cast non character columns to a chararcter data type.
From: Plamen Ratchev on 30 Mar 2010 09:24
You can use FOR XML PATH:

DECLARE @s NVARCHAR(MAX);

SET @s = STUFF((SELECT ', ' + HeadlineTitle
FROM @NonexistingHeadlines
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

--
Plamen Ratchev
http://www.SQLStudio.com
From: Andy B. on 30 Mar 2010 09:45
Will this get all of the rows for the column HeadlineTitle?

create table @NonexistingHeadlines(
HeadlineID int,
HeadlineTitle nvarchar(200),
primary key(HeadlineID),
unique key(HeadlineTitle))
go
insert into NonExistingHeadlines (HeadlineID,HeadlineTitle)
values(1,'test 1'),
(2,'test 2'),
(3,'test 3')
go

I need a string that looks something like this:
"The following Headlines were added to the database:

1. test 1
2. test 2
3. test 3"



"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:5Judnf9Iit0BZizWnZ2dnUVZ_tUAAAAA(a)speakeasy.net...
> You can use FOR XML PATH:
>
> DECLARE @s NVARCHAR(MAX);
>
> SET @s = STUFF((SELECT ', ' + HeadlineTitle
> FROM @NonexistingHeadlines
> FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2,
> '');
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 30 Mar 2010 10:10
Try this:

SET @s = N'The following Headlines were added to the database:' + NCHAR(13) + NCHAR(10) +
(SELECT CAST(HeadlineId AS NVARCHAR(10)) + N'. ' +
HeadlineTitle + NCHAR(13) + NCHAR(10)
FROM @NonexistingHeadlines
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Optimizing queries with isnull
Next: combine 2 selects in where clause