Solved

Using Coalesce and Group by in a view or query

Posted on 2014-09-25
5
153 Views
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
AS 
BEGIN
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + Animal
FROM dbo.tblAnimal
WHERE ID = @ID
SELECT @listStr AS Animals;
END

Open in new window


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

tblAnimal      
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?
0
Comment
Question by:databarracks
[X]
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
5 Comments
 
LVL 24

Expert Comment

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

Author Comment

by:databarracks
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?
0
 
LVL 24

Accepted Solution

by:
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
		   ORDER BY T.ID
		   for XML PATH('')),3,1000) [Animals]
from tblAnimal U

Open in new window

0
 

Author Comment

by:databarracks
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.

Bravo!!!
0
 

Author Closing Comment

by:databarracks
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
0

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. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
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