Using Coalesce and Group by in a view or query

Posted on 2014-09-25
Last Modified: 2014-09-25
Hi Guys,

Could someone please help me use a coalesce function in a query or view with a group by to produce the below results. I know how to do this without group by in a stored proc (basically for a single ID) but would like this in a view. The code I use for my proc is
CREATE PROCEDURE [dbo].[spGetAnimals]
@ID int
SELECT @listStr = COALESCE(@listStr+',' , '') + Animal
FROM dbo.tblAnimal
SELECT @listStr AS Animals;

Open in new window

My ideal result in a view not a proc would be as below:

ID      Animal
1      Cat
2      Cat
3      Cat
1      Dog
2      Dog
1      Hamster
3      Hamster
Result      Animals
1      Cat, Dog, Hamster
2      Cat, Dog
3      Cat, Hamster

Thank you for your help in advance?
Question by:databarracks
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 24

Expert Comment

by:Phillip Burton
ID: 40343538
Why do you want to do it without using GROUP BY?

Author Comment

ID: 40343542
Hi Phillip,

Well I simply assumed I needed group by to get my desired result. If there is another way, then please kindly show me?
LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40343549
How about this:

select distinct U.ID,
substring((select ', '+T.Animal as [text()]
		   from tblAnimal T
		   Where T.ID = U.ID
		   for XML PATH('')),3,1000) [Animals]
from tblAnimal U

Open in new window


Author Comment

ID: 40343551
Hi Phillip,

That is genius:) Works wonderfully and is super quick too.

Many thanks again for your help on this query as you did with my last request. I really appreciate your help on these queries.


Author Closing Comment

ID: 40343556
Very, very good solution and would like to thank Phillip for his help as he is very helpful, concise and patient. Well done Phillip you are great

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question