Return values from one field horizontally

[Palmyra]

Is there a way in SQL to return instances of a field horizontally? For instance:

SELECT id, index FROM table WHERE id = 'CC093509'
returns:

id index
CC093509 103773
CC093509 103774
CC093509 103775
CC093509 103776
CC093509 103777
CC093509 103778
CC093509 103779
CC093509 103780
CC093509 103781

I’d like it to return:

CC093509, 103773 10274 103775 103776 103777 103778 103779 103780 103781

Am I dreaming?

Thanks.

 

[markros]

Make a google search on 'Dynamic PIVOT T-SQL'

PluralSight Learning Library

 

[Palmyra]

Doesn't seem like I can do it with a simple query. I'd need to create a temp table and pass parameters.

 

[gmmastros]

thread183-1159740

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Here are 2 good resources... The for XML approach is the best..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Simi

 

[Palmyra]

SELECT DISTINCT p1.psdesc,
CONVERT(VARCHAR(255),REPLACE(
(
SELECT CONVERT(VARCHAR(30),psindex) + ',' AS 'data()'
FROM psearch p2
WHERE p2.psdesc = p1.psdesc
ORDER BY psdesc
FOR XML PATH('')
) + '$'
, ',$', '')) AS psindex_set
FROM psearch p1
WHERE psdesc = 'CC035897'
GROUP BY psdesc

returns:

psdesc psindex_set
CC035897 46028, 46029, 46030, 46031