Concatenating the Rows

Using Sql Server 2005

CREATE TABLE FRUITS

(

[ID] INT NOT NULL,

[NAME] VARCHAR(250) NOT NULL

)

INSERT INTO FRUITS VALUES(1,'Apple')

INSERT INTO FRUITS VALUES(2,'Banana')

INSERT INTO FRUITS VALUES(3,'Orange')

INSERT INTO FRUITS VALUES(4,'Melon')

INSERT INTO FRUITS VALUES(5,'Grape')

with list as (

  select

    name as value,

    row_number() over(order by id) as num

  from

    fruits

)

,concatenations as (

  select

    value as head,

    cast('' as varchar(MAX)) as tail,

    num,

    (select top 1 name from fruits order by id desc) as terminator

  from

    list

union all

  select

    head,

    (select value from list where num = prev.num - 1) + ', ' + tail as tail,

    num - 1,

    terminator

  from

    concatenations as prev

  where

    num > 0

    and head = terminator

)

 ,

concatenated as (

  select

    max(tail + head) as items

  from

    concatenations

  where

    num = 1

)

select * from concatenated

SQL - SERVER - 2008

@ValidExtension = COALESCE((SELECT TOP 1
    (
        SELECT DT.Extension  + ', ' as [text()]
        FROM
        dbo.ProjectDocTypes PDT
        INNER JOIN
        dbo.DocumentTypes DT ON PDT.[DocTypeId] = DT.[Id]
        WHERE ClientId =  U.ClientID AND ProjectId = U.DefaultProjectId  
        ORDER BY ClientId,ProjectId ASC FOR XML PATH('')
      ) AS ValidExtension),'') 

Select @ValidExtension