Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using Coalesce and Group by in a view or query

Posted on 2014-09-25
5
Medium Priority
?
156 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 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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…
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…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

670 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