Post reply

Need Help to Achive this

  • July 13, 2011 at 12:59 am

    #241480

    Please help me in getting the results as shown in the screen shot

  • July 13, 2011 at 2:28 am

    #1352007

    Sure! No problem.

    But we need something more than a screenshot in order to help you.

    Take a look at this article[/url] and you will learn how to post a question effectively.

    -- Gianluca Sartori

  • July 13, 2011 at 2:40 am

    #1352009

    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

    #1352013

    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

    #1352020

    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/61537

  • July 13, 2011 at 3:10 am

    #1352021

    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

    #1352023

    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/61537

  • July 13, 2011 at 3:23 am

    #1352024

    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

    #1352035

    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/61537

  • July 13, 2011 at 6:55 am

    #1352123

    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

    #1352587

    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