May 8, 2009 at 2:44 pm
If I run a query like
select animals from animay where id = 10
ther results are
cow
turky
chicken
is there a way to make the results apear in one cell as:
cow,turky,chicken
:w00t:
May 8, 2009 at 2:55 pm
There are a few ways to accomplish this. Here are some examples...
Example 1
Declare @Table Table (id int, animals varchar(10))
Insert Into @Table
Select 10, 'cow' UNION ALL
Select 10, 'chicken' UNION ALL
Select 10, 'turkey' UNION ALL
Select 11, 'turkey'
SELECT
t1.ID,
List = substring((SELECT ( ', ' + animals )
FROM @Table t2
WHERE t1.ID = t2.ID
ORDER BY ID
FOR XML PATH( '' )
), 3, 1000 )FROM @Table t1
Where ID = 10
GROUP BY ID
Example 2
Declare @Table Table (id int, animals varchar(10))
Insert Into @Table
Select 10, 'cow' UNION ALL
Select 10, 'chicken' UNION ALL
Select 10, 'turkey' UNION ALL
Select 11, 'turkey'
DECLARE @Animal VARCHAR(1000)
SELECT @Animal = ISNULL(@Animal,'') + animals + ';'
FROM @Table
WHERE id = 10
SELECT @Animal AS animals
I personally like the XML method.
May 20, 2009 at 8:57 am
Thanks for the reply. I really appreciate the help. After further research here is what I went with:
I made the following into a function, and boy does it work good.
DECLARE @List varchar(2000)
SELECT @List = COALESCE(@List + ',', '') + Cast(tblProductGroup.GroupDesc As varchar(15))
FROM tblProductGroup INNER JOIN
tblLevelProductRel ON tblProductGroup.GenGroupID = tblLevelProductRel.GroupID
WHERE tblLevelProductRel.LevelCloneID = @LevelCloneID
return @List
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply