Solved

Using Coalesce and Group by in a view or query

Posted on 2014-09-25
5
149 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
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Webservices in T-SQL 3 41
Add a step to a system backup job 6 30
SQL Server 2008 R2, need a pivot/cross tab query... 4 48
Why use this lambda? 12 60
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore 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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

680 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