MS SQL

SQL Question

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Mar 2007

Posts: 158

Reputation: vuyiswamb is an unknown quantity at this point

Solved Threads: 5

vuyiswamb

Offline

Junior Poster

SQL Question

May 7th, 2009

Good Morning All

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
ID     ||    DESCR              || CYCLE
======================================
    ||	Earl G          ||   20
    ||  	Earl G          ||   21
    ||	Earl G 	        ||   22
    ||	Davidson I Dr   ||   20
    ||	Davidson I Dr   ||   21
    ||	Davidson I Dr   ||   22
    ||	Easton C        ||   20
    || 	Easton C        ||   21
    || 	Easton C        ||   22
   ||	Edwards J Ms    ||   20
   ||	Edwards J Ms    ||   21
   ||	Edwards J Ms    ||   22
ID     ||    DESCR              || CYCLE
======================================
    ||	Earl G          ||   20
    ||  	Earl G          ||   21
    ||	Earl G 	        ||   22
    ||	Davidson I Dr   ||   20
    ||	Davidson I Dr   ||   21
    ||	Davidson I Dr   ||   22
    ||	Easton C        ||   20
    || 	Easton C        ||   21
    || 	Easton C        ||   22
   ||	Edwards J Ms    ||   20
   ||	Edwards J Ms    ||   21
   ||	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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

•

Join Date: May 2009

Posts: 196

Reputation: jbisono is an unknown quantity at this point

Solved Threads: 24

jbisono

Offline

Junior Poster

Re: SQL Question

May 7th, 2009

try this

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT ( SELECT f.cycle as col1
	FROM final as f
	for xml raw, elements, type).query('for $col1 in (row/col1) return concat($col1, " ")')
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.

Last edited by peter_budo; May 7th, 2009 at 3:29 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Mar 2007

Posts: 158

Reputation: vuyiswamb is an unknown quantity at this point

Solved Threads: 5

vuyiswamb

Offline

Junior Poster

Re: SQL Question

May 7th, 2009

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

i created a UDF like this

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT Descr,.dbo.GetCycle(Descr) AS Cycle FROM YourTableName WHERE Descr 
          IN ( SELECT DISTINCT Descr FROM YourTableName)
Select Descr,.dbo.GetCycle(Descr) As Cycle From YourTableName Where Descr 
          in ( select Distinct Descr From YourTableName)