Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

COALESCE coulmn in a group by select

In the select below...
Can I use a coalesce or stuff xml statement to get a comma separated column of i.IndividualID's?


SELECT   o.OriginName ,
         COUNT(*) Cnt ,
         MAX(i.DateAdded) MaxDateAdded ,
         GETDATE() AS currDate
FROM     Individuals i
         JOIN IndividualOrigins oo ON i.IndividualID = oo.IndividualID
         JOIN Origins o ON oo.OriginID = o.OriginID
WHERE    o.OriginName IN ( 'BGV LEADS', 'BGV OWNERS' )
         AND DATEDIFF(HH, i.DateModified, GETDATE()) <= 12
GROUP BY o.OriginName
HAVING   COUNT(i.IndividualID) > 0;

Open in new window

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Yes you can use STUFF and XML to get comma separated list of Ids. Can you show what you are getting and what you need.
example --

CREATE TABLE TestTable 
(
  StudentName VARCHAR(100)
, Course VARCHAR(100)
, Instructor VARCHAR(100)
, RoomNo VARCHAR(100)
)
GO
 
-- Populate table
 
INSERT INTO TestTable (StudentName, Course, Instructor, RoomNo)
SELECT 'Mark', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
UNION ALL
SELECT 'Joe', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Joe', 'Science', 'Dr. Ross', '301'
UNION ALL
SELECT 'Joe', 'Geography', 'Dr. Lisa', '401'
UNION ALL
SELECT 'Jenny', 'Algebra', 'Dr. James', '101'
GO

 
/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/
 
SELECT b.StudentName 
            , STUFF 
                ((
                SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + ' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))
                FROM TestTable a
                WHERE ( a.StudentName = b.StudentName )
                FOR XML PATH('')
                ) ,1,2,'') 
                AS cusr
FROM TestTable b
GROUP BY b.StudentName

Open in new window

Avatar of Larry Brister

ASKER

Pawar,
 Pretty simple...
Because of the join I can change Count(*) to Count(i.IndividualID) and there is no difference because there is a 1 to 1 relationship between Individuals and IndividualOrigins.

This is a screen print...
User generated image
So its done ?
No...
I need a column with

[IndividualIDList]
3277751,3277752,3277754,3277756,3277759,3277760
Please try this -
SELECT   o.OriginName ,
			 COUNT(*) Cnt ,
			 MAX(i.DateAdded) MaxDateAdded ,
			 GETDATE() AS currDate
			,STUFF 
                       (( 
					SELECT ', ' + CAST(oo.IndividualID AS VARCHAR(MAX))
					FROM IndividualOrigins oo
					WHERE ( i.IndividualID = oo.IndividualID )
					FOR XML PATH('')
                      ) ,1,1,'') 
                    AS [IndividualIds]	
	FROM     Individuals i
			 JOIN Origins o ON oo.OriginID = o.OriginID
	WHERE    o.OriginName IN ( 'BGV LEADS', 'BGV OWNERS' )
			 AND DATEDIFF(HH, i.DateModified, GETDATE()) <= 12
	GROUP BY o.OriginName
	HAVING   COUNT(i.IndividualID) > 0;

Open in new window

That did not work... the joins were incorrect.

I tried this... but just got a list

USE Everyware

DECLARE @hrs INT = 14

SELECT   og.OriginName ,
         COUNT(i.IndividualID) Cnt ,
         MAX(i.DateAdded) MaxDateAdded ,
         GETDATE() AS currDate,
		 STUFF 
                ((
					SELECT ', ' + CAST(oo.IndividualID AS VARCHAR(MAX))
					FROM IndividualOrigins oo
					WHERE ( i.IndividualID = oo.IndividualID )
					FOR XML PATH('')
                ) ,1,2,'') 
                AS cusr	
FROM     Individuals i
         JOIN IndividualOrigins o ON i.IndividualID = o.IndividualID
         JOIN Origins og ON o.OriginID = og.OriginID
WHERE    og.OriginName IN ( 'BGV LEADS', 'BGV OWNERS' )
         AND DATEDIFF(HH, i.DateModified, GETDATE()) <= @hrs
GROUP BY og.OriginName, i.IndividualID
HAVING   COUNT(i.IndividualID) > 0;

Open in new window


User generated image
Please try this -

;WITH CTE AS
(
	SELECT   o.OriginName ,
				 COUNT(*) Cnt ,
				 MAX(i.DateAdded) MaxDateAdded ,
				 GETDATE() AS currDate
				,STUFF 
					((
						SELECT ', ' + CAST(oo.IndividualID AS VARCHAR(MAX))
						FROM IndividualOrigins oo
						WHERE ( i.IndividualID = oo.IndividualID )
						FOR XML PATH('')
					) ,1,2,'') 
					AS cusr	
		FROM     Individuals i
				 JOIN Origins o ON oo.OriginID = o.OriginID
		WHERE    o.OriginName IN ( 'BGV LEADS', 'BGV OWNERS' )
				 AND DATEDIFF(HH, i.DateModified, GETDATE()) <= 12
		GROUP BY o.OriginName
		HAVING   COUNT(i.IndividualID) > 0;
),CTE1 AS
(
	SELECT OriginName , SUM(Cnt) Cnt, MAX(MaxDateAdded) MaxDateAdded , MAX(currDate) currDate
	FROM CTE
	GROUP BY OriginName
)
SELECT * ,STUFF 
					((
						SELECT ', ' + CAST(c1.cusr AS VARCHAR(MAX))
						FROM CTE1 c1
						WHERE ( c.OriginName = c1.OriginName )
						FOR XML PATH('')
					) ,1,1,'') 
					AS [IndividualIds]	
FROM 
CTE1 c

Open in new window

Pawan,
  The join must be from Individuals to IndividualOrigins
THEN
IndividualOrigins to Origins.

The Origin table is where I get the Origin Name

The IndividualOrigins is a simple two column table that is the link between Individuals and Origins
[IndividualID], [OriginID]

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hey Pawan,
 That put me on the right track.
I just had to change the final select.

USE [Everyware]
GO

DECLARE @hrs INT = 15
;WITH CTE AS
(
	SELECT   og.OriginName ,
			 COUNT(i.IndividualID) Cnt ,
			 MAX(i.DateAdded) MaxDateAdded ,
			 GETDATE() AS currDate,
			 STUFF 
					((
						SELECT ', ' + CAST(oo.IndividualID AS VARCHAR(MAX))
						FROM IndividualOrigins oo
						WHERE ( i.IndividualID = oo.IndividualID )
						FOR XML PATH('')
					) ,1,2,'') 
					AS cusr	
	FROM     Individuals i
			 JOIN IndividualOrigins o ON i.IndividualID = o.IndividualID
			 JOIN Origins og ON o.OriginID = og.OriginID
	WHERE    og.OriginName IN ( 'BGV LEADS', 'BGV OWNERS' )
			 AND DATEDIFF(HH, i.DateModified, GETDATE()) <= @hrs
	GROUP BY og.OriginName, i.IndividualID
	HAVING   COUNT(i.IndividualID) > 0
),CTE1 AS
(
	SELECT OriginName , SUM(Cnt) Cnt, MAX(MaxDateAdded) MaxDateAdded , MAX(currDate) currDate
	FROM CTE
	GROUP BY OriginName
)
SELECT c.OriginName ,
       MAX(c.MaxDateAdded) DateAdded ,
       MAX(c.currDate) currDate ,
       STUFF 
					((
						SELECT ', ' + CAST(c1.cusr AS VARCHAR(MAX))
						FROM CTE c1
						WHERE ( c.OriginName = c1.OriginName )
						FOR XML PATH('')
					) ,1,1,'') 
					AS [IndividualIds]	
FROM 
CTE c
GROUP BY c.OriginName

Open in new window

cool..  :)
Put me on the right track and I just had to modify slightly.

Thanks for sticking with me.