November 12, 2015 at 10:36 am
Hi,
I have a table
SELECT [day],[maxConcurUsers],[employeeid],[roleid],[Role Type]
FROM [applog].[dbo].[Aaroon_Test_a_maxConcurUsers]
its returns the following
daymaxConcurUsersemployeeidroleidRole Type
2015-11-11 13740029Customer Experience Team
2015-11-11 13740021QC Admin
2015-11-11 13740329Customer Experience Team
2015-11-11 13740321QC Admin
2015-11-11 1401398519CM Call Recordings
2015-11-11 140139854CM_Listening
2015-11-11 140139851000004CM_Listening (S)
2015-11-11 1401398550Tesco Advisor
2015-11-11 13740329Customer Experience Team
2015-11-11 13740321QC Admin
where the employeeids are the same , i need it to show on one row instead of crossing into 2 rows , so the end result should look something like this....
daymaxConcurUsersemployeeidroleid Role Type
2015-11-11 137400 29,21 Customer Experience Team,QC Admin
2015-11-11 137403 29,21 Customer Experience Team,QC Admin
help will be appreciated.
November 12, 2015 at 10:46 am
Is it always 1 or 2 rows? Or is it a variable number?
November 12, 2015 at 11:04 am
This should get you started.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
-- Itzik Ben-Gan 2001
November 12, 2015 at 11:53 am
Do you know what the stuff function does?
https://msdn.microsoft.com/en-us/library/ms188043.aspx
SELECT STUFF ( 'This is a lack of knowledge' , 11 , 7 , 'great' )
November 12, 2015 at 1:47 pm
Luis Cazares (11/12/2015)
Do you know what the stuff function does?https://msdn.microsoft.com/en-us/library/ms188043.aspx
SELECT STUFF ( 'This is a lack of knowledge' , 11 , 7 , 'great' )
Though STUFF is an insignificant and cosmetic part of the bigger solution, I was giving the OP the benefit of the doubt and assuming they had seen a solution for something like that that used STUFF. Such as:
DECLARE @missingDDL TABLE
(
[day]date,
maxConcurUsers int,
employeeid int,
roleid int,
[Role Type] varchar(100)
);
INSERT @missingDDL VAlUES
('2015-11-11',1,37400,29,'Customer Experience Team'),
('2015-11-11',1,37400,21,'QC Admin'),
('2015-11-11',1,37403,29,'Customer Experience Team'),
('2015-11-11',1,37403,21,'QC Admin'),
('2015-11-11',1,4013985,19,'CM Call Recordings'),
('2015-11-11',1,4013985,4,'CM_Listening'),
('2015-11-11',1,4013985,1000004,'CM_Listening (S)'),
('2015-11-11',1,4013985,50,'Tesco Advisor'),
('2015-11-11',1,37403,29,'Customer Experience Team'),
('2015-11-11',1,37403,21,'QC Admin');
SELECT
[day],
maxConcurUsers,
employeeid,
roleid =
STUFF
((
SELECT CONCAT(', ',roleid)
FROM @missingDDL b
WHERE a.[day]=b.[day]
AND a.employeeid=b.employeeid
FOR XML PATH(''), TYPE
).value('.','varchar(200)'),1,2,''),
[Role Type] =
STUFF
((
SELECT CONCAT(', ',[Role Type])
FROM @missingDDL b
WHERE a.[day]=b.[day]
AND a.employeeid=b.employeeid
FOR XML PATH(''), TYPE
).value('.','varchar(2000)'),1,2,'')
FROM @missingDDL a
GROUP BY [day], maxConcurUsers, employeeid;
-- Itzik Ben-Gan 2001
November 12, 2015 at 1:54 pm
Alan.B (11/12/2015)
Luis Cazares (11/12/2015)
Do you know what the stuff function does?https://msdn.microsoft.com/en-us/library/ms188043.aspx
SELECT STUFF ( 'This is a lack of knowledge' , 11 , 7 , 'great' )
Though STUFF is an insignificant and cosmetic part of the bigger solution, I was giving the OP the benefit of the doubt and assuming they had seen a solution for something like that that used STUFF.
I totally get it, and I'm sure the OP knew about this solution. But I posted that because it's not the first time I see someone believe that the STUFF is doing the concatenation. I would have shared the same article that you posted if it wasn't already there. This technique has different parts that conform a great solution, but if you don't understand it completely, there are so many places to make mistakes.
November 13, 2015 at 2:04 am
variable number
November 16, 2015 at 2:53 pm
Alan.B (11/12/2015)
This should get you started.http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
Using your data, and building off of this article (man, that really is an awesome article, isn't it???), you end up with:
WITH cte AS
(
SELECT *
FROM (VALUES ('2015-11-11', 1, 37400, 29, 'Customer Experience Team'),
('2015-11-11', 1, 37400, 21, 'QC Admin'),
('2015-11-11', 1, 37403, 29, 'Customer Experience Team'),
('2015-11-11', 1, 37403, 21, 'QC Admin'),
('2015-11-11', 1, 4013985, 19, 'CM Call Recordings'),
('2015-11-11', 1, 4013985, 4, 'CM_Listening'),
('2015-11-11', 1, 4013985, 1000004, 'CM_Listening (S)'),
('2015-11-11', 1, 4013985, 50, 'Tesco Advisor'),
('2015-11-11', 1, 37403, 29, 'Customer Experience Team'),
('2015-11-11', 1, 37403, 21, 'QC Admin')
) dt(day, maxConcurUsers, employeeid, roleid, [Role Type])
)
/*
daymaxConcurUsersemployeeidroleid Role Type
2015-11-11 137400 29,21 Customer Experience Team,QC Admin
2015-11-11 137403 29,21 Customer Experience Team,QC Admin
*/
SELECT DISTINCT
day, maxConcurUsers, employeeid,
STUFF((SELECT ',' + CONVERT(VARCHAR(15),t2.roleid)
FROM cte t2
WHERE t1.day = t2.day
AND t1.employeeid = t2.employeeid
AND t1.maxConcurUsers = t2.maxConcurUsers
ORDER BY roleid
FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
,1,1,''),
STUFF((SELECT ',' + t2.[Role Type]
FROM cte t2
WHERE t1.day = t2.day
AND t1.employeeid = t2.employeeid
AND t1.maxConcurUsers = t2.maxConcurUsers
ORDER BY roleid
FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
,1,1,'')
FROM cte t1;
Which gives these results:
day maxConcurUsers employeeid
---------- -------------- ----------- --------------- -------------------------------------------------------------------
2015-11-11 1 37400 21,29 QC Admin,Customer Experience Team
2015-11-11 1 37403 21,21,29,29 QC Admin,QC Admin,Customer Experience Team,Customer Experience Team
2015-11-11 1 4013985 4,19,50,1000004 CM_Listening,CM Call Recordings,Tesco Advisor,CM_Listening (S)
Is this what you're looking for?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply