SQL Question

[vuyiswamb]

Good Morning All

I have a table named “Final” with Values like this

ID     ||    DESCR              || CYCLE
======================================
1      ||	Earl G          ||   20
2      ||  	Earl G          ||   21
3      ||	Earl G 	        ||   22
4      ||	Davidson I Dr   ||   20
5      ||	Davidson I Dr   ||   21
6      ||	Davidson I Dr   ||   22
7      ||	Easton C        ||   20
8      || 	Easton C        ||   21
9      || 	Easton C        ||   22
10     ||	Edwards J Ms    ||   20
11     ||	Edwards J Ms    ||   21
12     ||	Edwards J Ms    ||   22

As you can see in the Description Field “Earl G” appears 3 times and it can be more than that. Now I want to append Cycles [cycle] strings to the corresponding description, I want to run a query so that this table may look like this

ID     ||    DESCR     || CYCLE
==============================================
1      ||Earl G        ||   20 , 21, 22
4      ||Davidson I Dr ||   20 , 21, 22
7      ||Easton C      ||   20 , 21, 22
10     ||Edwards J Ms  ||   20 , 21, 22

How can I achieve this in SQL

Thank you

[jbisono]

try this

select ( select f.cycle as col1
	from final as f
	for xml raw, elements, type).query('for $col1 in (row/col1) return concat($col1, " ")')

I did not test this.

Regards.

[vuyiswamb]

i have Resolved the Problem. Thank you. i did the Following

i created a UDF like this

CREATE FUNCTION GetCycle (@Descr Varchar(50))  
RETURNS Varchar(500)
AS  
BEGIN
Declare @RetStr as varchar(500),@Cycle Int  --<-- Assuming Cycle field  is of Type Integer

Declare TmpCur Cursor For select CyCle From YourTblName Where Descr=@Descr
Open TmpCur
Set @RetStr=''
Fetch Next From TmpCur Into @Cycle
While @@Fetch_status=0
Begin
		Set @RetStr = @RetStr + Case when @RetStr='' then '' else ' ' End + Cast(@Cycle as varchar)
	Fetch Next From TmpCur Into @Cycle
End
Close TmpCur 
Deallocate TmpCur 
return (@RetStr)
END

and Called the Function like this

Select Descr,.dbo.GetCycle(Descr) As Cycle From YourTableName Where Descr 
          in ( select Distinct Descr From YourTableName)

Thank you