Concatenating Columns from a Result Set in SQL Server 2000

Question

I have a query that returns data in the following form

attribute       value
---------      ----------
petid           1000
name            buttercup
species         cat
age             10
owner           Bob Dole

Basically I want to go through every row and return the fields name and species in a single string, so here the result would be

buttercup cat

Any ideas how I could do this?

Answer 1

Okay - Now I think I understand the data format...

Here is the code to create the sample set (just to make sure I've got it right)

CREATE TABLE MyTable
(
attribute varchar(20),
value varchar(20)
)

INSERT INTO MyTable VALUES('petid','1000')
INSERT INTO MyTable VALUES('name','buttercup')
INSERT INTO MyTable VALUES('species','cat')
INSERT INTO MyTable VALUES('age','10')
INSERT INTO MyTable VALUES('owner','Bob Dole')

Here is my answer:

SELECT a.value + ' ' +b.value
FROM MyTable AS a
INNER JOIN MyTable AS b ON a.attribute='name' AND b.attribute = 'species'

Answer 2

Try this. I've only tried it with SQL Serer 2008, but maybe it will work:

DECLARE @Concat nvarchar(50)
SET @Concat=N''

SELECT @Concat = @Concat + Value + N' '
FROM dbo.AttributeValue
WHERE Attribute IN (N'name', N'species')

SELECT @Concat

Answer 3

The cursor way of doing this would be some thing like this-

DECLARE @name varchar(20) 
DECLARE @species varchar(20)    
DECLARE nameSpeciesCursor CURSOR FOR 
SELECT name, species FROM tableName

OPEN nameSpeciesCursor 
FETCH NEXT FROM nameSpeciesCursor INTO @name, @species  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       PRINT @name + ' ' + @species    
       FETCH NEXT FROM nameSpeciesCursor INTO @name, @species  
END  

CLOSE nameSpeciesCursor 
DEALLOCATE nameSpeciesCursor

cheers