Larry Brister
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?
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;
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
ASKER
So its done ?
ASKER
No...
I need a column with
[IndividualIDList]
3277751,3277752,3277754,32 77756,3277 759,327776 0
I need a column with
[IndividualIDList]
3277751,3277752,3277754,32
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;
ASKER
That did not work... the joins were incorrect.
I tried this... but just got a list
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;
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
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey Pawan,
That put me on the right track.
I just had to change the final select.
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
cool.. :)
ASKER
Put me on the right track and I just had to modify slightly.
Thanks for sticking with me.
Thanks for sticking with me.