July 13, 2011 at 12:59 am
Please help me in getting the results as shown in the screen shot
July 13, 2011 at 2:28 am
July 13, 2011 at 2:40 am
Here you go:
SELECT 'Employee1' AS [Employees], 'Car' AS [ModeOfTransport]
UNION ALL
SELECT 'Employee1', 'Bus'
UNION ALL
SELECT 'Employee1', 'Train'
UNION ALL
SELECT 'Employee2', 'Car'
UNION ALL
SELECT 'Employee2', 'Bus'
UNION ALL
SELECT 'Employee3', 'Train'
UNION ALL
SELECT 'Employee4', 'Car'
UNION ALL
SELECT 'Employee4', 'Bus'
UNION ALL
SELECT 'Employee4', 'Train'
UNION ALL
SELECT 'Employee5', 'Car'
UNION ALL
SELECT 'Employee5', 'Bus'
UNION ALL
SELECT 'Employee5', 'Train'
UNION ALL
SELECT 'Employee6', 'Car'
UNION ALL
SELECT 'Employee6', 'Bus'
UNION ALL
SELECT 'Employee7', 'Train'
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 13, 2011 at 2:50 am
Define Group1, Group2 and Group3. What are the cryteria ?
Are there any other groups ?
Is your grouping dynamic ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 13, 2011 at 3:02 am
Try this
WITH Employees(Employee,ModeOfTransport) AS (
SELECT 'Employee1' , 'Car'
UNION ALL
SELECT 'Employee1', 'Bus'
UNION ALL
SELECT 'Employee1', 'Train'
UNION ALL
SELECT 'Employee2', 'Car'
UNION ALL
SELECT 'Employee2', 'Bus'
UNION ALL
SELECT 'Employee3', 'Train'
UNION ALL
SELECT 'Employee4', 'Car'
UNION ALL
SELECT 'Employee4', 'Bus'
UNION ALL
SELECT 'Employee4', 'Train'
UNION ALL
SELECT 'Employee5', 'Car'
UNION ALL
SELECT 'Employee5', 'Bus'
UNION ALL
SELECT 'Employee5', 'Train'
UNION ALL
SELECT 'Employee6', 'Car'
UNION ALL
SELECT 'Employee6', 'Bus'
UNION ALL
SELECT 'Employee7', 'Train'),
Grouped AS (
SELECT e1.Employee,
(SELECT e2.ModeOfTransport+'/' AS "text()"
FROM Employees e2
WHERE e2.Employee=e1.Employee
ORDER BY e2.ModeOfTransport
FOR XML PATH('')) AS AllModes
FROM Employees e1
GROUP BY e1.Employee)
SELECT 'Group'+CAST(ROW_NUMBER() OVER(ORDER BY MIN(g1.Employee)) AS VARCHAR(10)) AS [Group],
(SELECT g2.Employee+',' AS "text()"
FROM Grouped g2
WHERE g2.AllModes=g1.AllModes
ORDER BY g2.Employee
FOR XML PATH('')) AS GroupMembers
FROM Grouped g1
GROUP BY g1.AllModes
ORDER BY MIN(g1.Employee);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537July 13, 2011 at 3:10 am
Very nice solution Mark. :smooooth:
I hope OP understands the performance impact.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 13, 2011 at 3:13 am
ALZDBA (7/13/2011)
Very nice solution Mark. :smooooth:I hope OP understands the performance impact.
Yep, I suspect there's a fast way to do this without having to group by a concatenated string. Can't see it at the moment though.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537July 13, 2011 at 3:23 am
Mark-101232 (7/13/2011)
ALZDBA (7/13/2011)
Very nice solution Mark. :smooooth:I hope OP understands the performance impact.
Yep, I suspect there's a fast way to do this without having to group by a concatenated string. Can't see it at the moment though.
Maybe, with a bitmapping solution on the Modes could have good perf results, but then again, volatility always comes with a price.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 13, 2011 at 4:11 am
Second attempt, sort of 'relational division', should be quicker than the first
WITH Employees(Employee,ModeOfTransport) AS (
SELECT 'Employee1' , 'Car'
UNION ALL
SELECT 'Employee1', 'Bus'
UNION ALL
SELECT 'Employee1', 'Train'
UNION ALL
SELECT 'Employee2', 'Car'
UNION ALL
SELECT 'Employee2', 'Bus'
UNION ALL
SELECT 'Employee3', 'Train'
UNION ALL
SELECT 'Employee4', 'Car'
UNION ALL
SELECT 'Employee4', 'Bus'
UNION ALL
SELECT 'Employee4', 'Train'
UNION ALL
SELECT 'Employee5', 'Car'
UNION ALL
SELECT 'Employee5', 'Bus'
UNION ALL
SELECT 'Employee5', 'Train'
UNION ALL
SELECT 'Employee6', 'Car'
UNION ALL
SELECT 'Employee6', 'Bus'
UNION ALL
SELECT 'Employee7', 'Train'),
CountedEmployees AS (
SELECT Employee,ModeOfTransport,
COUNT(*) OVER(PARTITION BY Employee) AS cn
FROM Employees),
Grouped AS (
SELECT a.Employee AS Employee1,
b.Employee AS Employee2,
a.ModeOfTransport,
COUNT(*) OVER(PARTITION BY a.Employee,b.Employee) AS cnGrp,
a.cn
FROM CountedEmployees a
INNER JOIN CountedEmployees b ON b.Employee<>a.Employee
AND b.ModeOfTransport=a.ModeOfTransport
AND a.cn=b.cn)
SELECT 'Group'+CAST(ROW_NUMBER() OVER(ORDER BY MIN(c1.Employee1)) AS VARCHAR(10)) AS [Group],
c1.Employee1+(SELECT DISTINCT ','+c2.Employee2 AS "text()"
FROM Grouped c2
WHERE c2.Employee1=c1.Employee1
FOR XML PATH('')) AS GroupMembers
FROM Grouped c1
WHERE c1.cn=c1.cnGrp
GROUP BY c1.Employee1
HAVING c1.Employee1<MIN(c1.Employee2)
ORDER BY c1.Employee1;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537July 13, 2011 at 6:55 am
FWIW maybe this way can also be given a try if you have a larger volume of data to process.
As shown in the script it is based on Adventureworks for SQL2008R2.
use AdventureWorksSQL2008R2
;
/* determine department bitvalue */
with cteDepartments
as (
SELECT D.[DepartmentID]
, D.Name
, power(2, ROW_NUMBER() over ( order by min(D.Name) )) as BitValue
FROM HumanResources.Department D
group by D.[DepartmentID]
, D.Name
) ,
/* determine Employee grouping value */
cteEmployeeGrouping
as (
Select EDH.EmployeeID
, SUM(distinct D.BitValue) as GroupingValue
, COUNT(*) as nDepartments
from HumanResources.EmployeeDepartmentHistory EDH
inner join cteDepartments D
on D.DepartmentID = EDH.DepartmentID
group by EDH.EmployeeID
) ,
/* determine Departments in a group */
cteGroupingDepartments
as (
SELECT EG.GroupingValue
, (
SELECT distinct
D.[Name] + '/' AS "text()"
FROM cteDepartments D
inner join HumanResources.EmployeeDepartmentHistory EDH
on EDH.EmployeeID = EG.EmployeeID
and EDH.DepartmentID = D.DepartmentID
where EG.GroupingValue & D.BitValue = D.BitValue
ORDER BY D.[Name] + '/'
FOR
XML PATH('')
) AS GroupMembers
from cteEmployeeGrouping EG
) ,
/* determine group Groupnumber */
cteGroups
as (
Select GroupingValue
, min(GroupMembers) as GroupMembers
, 'Group' + RIGHT(cast(ROW_NUMBER() over ( order by ( GroupingValue ) )+ 1000000 as varchar(10)), 5) as GroupName
from cteGroupingDepartments
group by GroupingValue
)
/* Present the data */
SELECT GroupName
, G.GroupMembers
, (
SELECT C.LastName + N', ' + FirstName + N' [' + cast(EG.EmployeeID as Nvarchar(128)) + N'] - ' AS "text()"
FROM cteEmployeeGrouping EG
inner join Person.Contact C
on C.ContactID = EG.EmployeeID
WHERE EG.GroupingValue = G.GroupingValue
ORDER BY EG.EmployeeID
FOR XML PATH('')
) AS GroupMembers
FROM cteGroups G
ORDER BY G.GroupName ;
Happy Performance Testing !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 14, 2011 at 12:33 am
Thank you very much for help i got solution for my problem from above examples
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply