Group By And Stuff Combined Result In Sql Server

I referred the this LINK but when I try to use the same format I am confused whether I have to use duplicate query or not.Becuase query am having is too lengthy.Please help me on t

Solution 1:

I think this is the best way to archive your goal.

SELECT is_active, paid,
tags_title = STUFF(
             (SELECT','+ tags_title FROMtableFOR XML PATH ('')), 1, 1, ''
           ) 
FROMtableGROUPBY is_active, paid

Solution 2:

By adding the STUFF field, maybe you can avoid to use:

LEFTJOIN task_tracker_tags ttt 
    ON ttt.tags_id = tttm.tags_id


SELECT proj.is_active, 
       pal.paid, 

       title = STUFF(
                 (SELECT','+ ttt.tags_title 
                  FROM task_tracker_tags ttt
                  WHERE ttt.tags_id = tttm.tags_id
                  FOR XML PATH ('')), 1, 1, '') 

FROM   person_aim_list pal 
       LEFTJOIN time_tace_map ttm 
              ON ttm.artifact_id = pal.paid 
       LEFTJOIN empires emp 
              ON emp.empire_id = ttm.empire_id 
       LEFTJOIN task_tracker_tags_mapper tttm 
              ON tttm.artifact_id = pal.paid 
       LEFTJOIN projects proj 
              ON proj.project_id = tttm.project_id 
WHERE  pal.child_priority IN ( 'High', 'Low' ) 
       AND pal.artifact_status IN ( 'Open' ) 
       AND emp.empire_id IN ( 3932, 1733 ) 
       AND proj.is_active =1AND pal.due_date <='01/02/2017'AND pal.due_date >='01/01/1800'GROUPBY proj.is_active, 
         pal.paid;

Solution 3:

You can do this like

SELECTDISTINCT proj.is_active, 
       pal.paid
       , STUFF((
            SELECT','+ ttt.tags_title
            FROM task_tracker_tags T
            WHERE T.tags_id = tttm.tags_id
            FOR XML PATH('')
            ), 1, 1, '') AS tags_title

FROM   person_aim_list pal 
       LEFTJOIN time_tace_map ttm 
              ON ttm.artifact_id = pal.paid 
       LEFTJOIN empires emp 
              ON emp.empire_id = ttm.empire_id 
       LEFTJOIN task_tracker_tags_mapper tttm 
              ON tttm.artifact_id = pal.paid 
       /* removed
       LEFT JOIN task_tracker_tags ttt 
              ON ttt.tags_id = tttm.tags_id 
       */LEFTJOIN projects proj 
              ON proj.project_id = tttm.project_id 
WHERE  pal.child_priority IN ( 'High', 'Low' ) 
       AND pal.artifact_status IN ( 'Open' ) 
       AND emp.empire_id IN ( 3932, 1733 ) 
       AND proj.is_active =1AND pal.due_date <='01/02/2017'AND pal.due_date >='01/01/1800'

Edit:

In your Update, You mentioned 4 columns like

SELECTDISTINCT proj.is_active, tttm.tags_id,
       pal.project_artifact_id
       , STUFF((
            SELECT','+ t.tags_title
            FROM task_tracker_tags T
            WHERE T.tags_id = tttm.tags_id
            FOR XML PATH('')
            ), 1, 1, '') AS tags_title

You need to change it to

SELECTDISTINCT proj.is_active ---, tttm.tags_id,  Here your query making wrong output.
       ,pal.project_artifact_id
       , STUFF((
            SELECT','+ t.tags_title
            FROM task_tracker_tags T
            WHERE T.tags_id = tttm.tags_id
            FOR XML PATH('')
            ), 1, 1, '') AS tags_title