none
how to group results in a single field

    Question

  • please help me to execute query In sql server 2008

    Please look at this table 

    How to display result in below format

    Regards,

    Salman

    Tuesday, February 18, 2014 3:57 PM

Answers

  • Something like this should work
    use demodb
    DECLARE @EMP_VISIT TABLE(fk_emp_id INT,visit_day INT,visit_order INT,fk_cust_id INT)
    INSERT INTO @EMP_VISIT VALUES
    (1,1,1,476),
    (1,5,1,476),
    (1,8,1,476),
    (1,11,1,476),
    (268,2,1,476),
    (268,8,1,476);
    
    
    declare @refcal table(week_id int,day_id int,day_name varchar(100));
    insert into @refcal values(1,1,'1st Saturday'),(1,5,'1st Wednesday');
    
    SELECT fk_cust_id cust_id,fk_emp_id emp_id,stuff((select ','+convert(varchar(30),r.day_name) from  @EMP_VISIT EI join @refcal R on EI.visit_day=r.day_id
    where ei.fk_cust_id=eo.fk_cust_id and eo.fk_emp_id=ei.fk_emp_id for xml path('')),1,1,'') FROM @EMP_VISIT EO
    group by fk_cust_id,fk_emp_id  



    Satheesh
    My Blog | How to ask questions in technical forum


    • Marked as answer by salmanpc Wednesday, February 19, 2014 1:01 PM
    Wednesday, February 19, 2014 12:06 PM
  • This question is asked here a lot.

    Try

    select E.FK_Cust_ID as Cust_ID, E.FK_emp_ED as Emp_ID,

    stuff((select ',' + cast(Visit_Day as varchar(10))

    FROM Emp_Visit E1 where E1.FK_Emp_ID = E.FK_Emp_ID and E1.FK_Cust_ID = E.FK_Cust_ID order by Visit_Day

    FOR XML PATH('')), 1,1,'') as [Visit Days] FROM dbo.Emp_Visit E

    GROUP BY E.FK_Cust_ID, E.FK_EMP_ID

    ORDER BY E.FK_Cust_ID, E.FK_EMP_ID




    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Praveen Rayan D'sa Tuesday, February 18, 2014 4:08 PM
    • Marked as answer by salmanpc Tuesday, February 18, 2014 4:21 PM
    Tuesday, February 18, 2014 4:03 PM
  • SELECT CustID,
    EmpID,
    STUFF((SELECT ',' + vc.Day_Name
           FROM ReferenceCalendar rc
           INNER JOIN Emp_Visit ev
           ON ev.Visit_Day = rc.Day_ID
           WHERE FK_CUst_ID = e.CustID
           AND FK_Emp_ID = e.EmpID 
           ORDER BY ev.VISIT_ORDER
           FOR XML PATH('')),1,1,'') AS VisitDays
    FROM (SELECT DISTINCT FK_CUst_ID AS CustID, FK_Emp_ID AS EmpID FROM Emp_Visit)e


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by salmanpc Wednesday, February 19, 2014 3:49 PM
    Wednesday, February 19, 2014 1:57 PM

All replies

  • This question is asked here a lot.

    Try

    select E.FK_Cust_ID as Cust_ID, E.FK_emp_ED as Emp_ID,

    stuff((select ',' + cast(Visit_Day as varchar(10))

    FROM Emp_Visit E1 where E1.FK_Emp_ID = E.FK_Emp_ID and E1.FK_Cust_ID = E.FK_Cust_ID order by Visit_Day

    FOR XML PATH('')), 1,1,'') as [Visit Days] FROM dbo.Emp_Visit E

    GROUP BY E.FK_Cust_ID, E.FK_EMP_ID

    ORDER BY E.FK_Cust_ID, E.FK_EMP_ID




    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Praveen Rayan D'sa Tuesday, February 18, 2014 4:08 PM
    • Marked as answer by salmanpc Tuesday, February 18, 2014 4:21 PM
    Tuesday, February 18, 2014 4:03 PM
  • Try this,

    DECLARE @EMP_VISIT TABLE(fk_emp_id INT,visit_day INT,visit_order INT,fk_cust_id INT)
    INSERT INTO @EMP_VISIT VALUES
    (1,2,1,476),
    (1,5,1,476),
    (1,8,1,476),
    (1,11,1,476),
    (268,2,1,476),
    (268,8,1,476);
    
    SELECT fk_cust_id cust_id,fk_emp_id emp_id,stuff((select ','+convert(varchar(30),visit_day) from  @EMP_VISIT EI where ei.fk_cust_id=eo.fk_cust_id and eo.fk_emp_id=ei.fk_emp_id for xml path('')),1,1,'') FROM @EMP_VISIT EO
    group by fk_cust_id,fk_emp_id  


    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, February 18, 2014 4:13 PM
  • Dear Naomi N , thanks for your reply ,

    if i want to display the visit_days based on reference calender 

    result should be like this ,

    how it works !!

    Regards,

    Salman

    Tuesday, February 18, 2014 4:21 PM
  • Can you post the structure of the Reference Calendar table?

    If it's just the regular Calendar with WeekDayName and WeekNumber, you just join with that table first and then use the same idea.

    If the Calendar table has 7 columns, then you would need to unpivot it first in order to normally join.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, February 18, 2014 4:25 PM
  • If so, just check datename function and also this answer

    http://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, February 18, 2014 6:54 PM
  • Please look at the structure of Reference Calendar table

    Wednesday, February 19, 2014 11:57 AM
  • Something like this should work
    use demodb
    DECLARE @EMP_VISIT TABLE(fk_emp_id INT,visit_day INT,visit_order INT,fk_cust_id INT)
    INSERT INTO @EMP_VISIT VALUES
    (1,1,1,476),
    (1,5,1,476),
    (1,8,1,476),
    (1,11,1,476),
    (268,2,1,476),
    (268,8,1,476);
    
    
    declare @refcal table(week_id int,day_id int,day_name varchar(100));
    insert into @refcal values(1,1,'1st Saturday'),(1,5,'1st Wednesday');
    
    SELECT fk_cust_id cust_id,fk_emp_id emp_id,stuff((select ','+convert(varchar(30),r.day_name) from  @EMP_VISIT EI join @refcal R on EI.visit_day=r.day_id
    where ei.fk_cust_id=eo.fk_cust_id and eo.fk_emp_id=ei.fk_emp_id for xml path('')),1,1,'') FROM @EMP_VISIT EO
    group by fk_cust_id,fk_emp_id  



    Satheesh
    My Blog | How to ask questions in technical forum


    • Marked as answer by salmanpc Wednesday, February 19, 2014 1:01 PM
    Wednesday, February 19, 2014 12:06 PM
  • SELECT CustID,
    EmpID,
    STUFF((SELECT ',' + vc.Day_Name
           FROM ReferenceCalendar rc
           INNER JOIN Emp_Visit ev
           ON ev.Visit_Day = rc.Day_ID
           WHERE FK_CUst_ID = e.CustID
           AND FK_Emp_ID = e.EmpID 
           ORDER BY ev.VISIT_ORDER
           FOR XML PATH('')),1,1,'') AS VisitDays
    FROM (SELECT DISTINCT FK_CUst_ID AS CustID, FK_Emp_ID AS EmpID FROM Emp_Visit)e


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by salmanpc Wednesday, February 19, 2014 3:49 PM
    Wednesday, February 19, 2014 1:57 PM