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
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backups - Strategies 1 21
Haw to apply join on 2 tables with this scenario 4 22
SQL Update Query - What's wrong with this. 18 20
SQL Server stored proc 2 10
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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.…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now