On Feb 23, 10:04 am, "mark4asp" <mark4...@gmail.com> wrote:
> Is there a convenient way to convert a small single column table of
> zero to six rows of VarChar(50) data to a single string via a
> function. Each item is ideally separated by semi-colon space.
>
> Do I need to use a cursor to do this?

On 24 Feb, 01:00, "Steve" <morrisz...@hotmail.com> wrote:
> On Feb 23, 10:04 am, "mark4asp" <mark4...@gmail.com> wrote:
>
> > Is there a convenient way to convert a small single column table of
> > zero to six rows of VarChar(50) data to a single string via a
> > function. Each item is ideally separated by semi-colon space.
>
> > Do I need to use a cursor to do this?
>
> --SQL SERVER 2005
> DECLARE @str varchar(4000)
> SET @str = (SELECT d.name + ','
> FROM HumanResources.Department d
> ORDER BY d.name
> FOR XML PATH(''))
> SET @str = SUBSTRING(@str,1,LEN(@str)-1)
> SELECT @str

> --SQL SERVER 2000
> DECLARE @name varchar(50)
> DECLARE @str varchar(4000)
> SET @str = ''
> DECLARE Build CURSOR
> LOCAL
> FORWARD_ONLY
> FOR
> SELECT d.name
> FROM HumanResources.Department d
> ORDER BY d.name
> OPEN Build
> FETCH NEXT FROM Build INTO @name
> WHILE @@FETCH_STATUS = 0
> BEGIN
> SET @str = @str + @name + ','
> FETCH NEXT FROM Build INTO @name
> END
> CLOSE Build
> DEALLOCATE Build
> SET @str = SUBSTRING(@str,1,LEN(@str)-1)
> SELECT @str

On Feb 26, 2:59 am, "mark4asp" <mark4...@gmail.com> wrote:
> On 24 Feb, 01:00, "Steve" <morrisz...@hotmail.com> wrote:
>
>
>
>
>
> > On Feb 23, 10:04 am, "mark4asp" <mark4...@gmail.com> wrote:
>
> > > Is there a convenient way to convert a small single column table of
> > > zero to six rows of VarChar(50) data to a single string via a
> > > function. Each item is ideally separated by semi-colon space.
>
> > > Do I need to use a cursor to do this?
>
> > --SQL SERVER 2005
> > DECLARE @str varchar(4000)
> > SET @str = (SELECT d.name + ','
> > FROM HumanResources.Department d
> > ORDER BY d.name
> > FOR XML PATH(''))
> > SET @str = SUBSTRING(@str,1,LEN(@str)-1)
> > SELECT @str
>
> This almost works but it wraps each item in XML tags which then need
> to be removed:
> DECLARE @str varchar(4000)
> SET @str = ( SELECT Activity FROM ActivityTypeTable ORDER BY Activity
> FOR XML PATH('') )
> SET @str = replace (@str, '</Activity><Activity>', '; ')
> SET @str = replace (@str, '</Activity>', '')
> SET @str = replace (@str, '<Activity>', '')
> SELECT @str
>
> Or more cryptically:
>
> DECLARE @str varchar(4000)
> SET @str = ( SELECT Activity FROM ActivityTypeTable ORDER BY Activity
> FOR XML PATH('') )
> SELECT replace (replace (replace (@str, '</Activity><Activity>', ';
> '), '</Activity>', ''), '<Activity>', '')
>
>
>
>
>
> > --SQL SERVER 2000
> > DECLARE @name varchar(50)
> > DECLARE @str varchar(4000)
> > SET @str = ''
> > DECLARE Build CURSOR
> > LOCAL
> > FORWARD_ONLY
> > FOR
> > SELECT d.name
> > FROM HumanResources.Department d
> > ORDER BY d.name
> > OPEN Build
> > FETCH NEXT FROM Build INTO @name
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
> > SET @str = @str + @name + ','
> > FETCH NEXT FROM Build INTO @name
> > END
> > CLOSE Build
> > DEALLOCATE Build
> > SET @str = SUBSTRING(@str,1,LEN(@str)-1)
> > SELECT @str
>
> I bet this second one actually takes less time !
>
> Thanks Steve- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -