CODEWITHSUNDEEP
sqlsql-server
sunleo
sunleo

Reputation: 10943

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 this.

My query gives below result:

is_active,    paid,  tags_title 
1             20    Testing
1             20    Development
1             21    Development
1             21    Testing
1             22    UI

Required result:

is_active,    paid,  tags_title 
1             20    Testing, Development
1             21    Testing, Development
1             22    UI

Update Exact query below:

SELECT DISTINCT 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

  FROM project_artifact_list pal 
LEFT JOIN task_tracker_mapper ttm 
ON ttm.artifact_id = pal.Project_Artifact_Id 
LEFT JOIN employees emp 
ON emp.employee_id = ttm.employee_id 
LEFT JOIN task_tracker_tags_mapper tttm  
ON tttm.artifact_id = pal.Project_Artifact_id 
LEFT JOIN projects proj
ON  proj.project_id = tttm.project_id 
WHERE  pal.Child_Priority IN ('High','Low')
AND pal.artifact_status IN ('Open')
AND emp.employee_id  IN (3932,1733)
AND proj.is_active = 1
AND pal.Due_Date <= '01/02/2017' 
AND pal.Due_Date >= '01/01/1800' 
GROUP BY proj.is_active,Project_Artifact_Id,tttm.tags_id;

Upvotes: 3

Views: 18715

Answers (3)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

You can do this like

SELECT DISTINCT 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 
       LEFT JOIN time_tace_map ttm 
              ON ttm.artifact_id = pal.paid 
       LEFT JOIN empires emp 
              ON emp.empire_id = ttm.empire_id 
       LEFT JOIN 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 
       */
       LEFT JOIN 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 = 1 
       AND pal.due_date <= '01/02/2017' 
       AND pal.due_date >= '01/01/1800'

Edit:

In your Update, You mentioned 4 columns like

SELECT DISTINCT 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 

SELECT DISTINCT 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

Upvotes: 2

McNets
McNets

Reputation: 10807

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

LEFT JOIN 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 
       LEFT JOIN time_tace_map ttm 
              ON ttm.artifact_id = pal.paid 
       LEFT JOIN empires emp 
              ON emp.empire_id = ttm.empire_id 
       LEFT JOIN task_tracker_tags_mapper tttm 
              ON tttm.artifact_id = pal.paid 
       LEFT JOIN 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 = 1 
       AND pal.due_date <= '01/02/2017' 
       AND pal.due_date >= '01/01/1800'
GROUP BY proj.is_active, 
         pal.paid;

Upvotes: 0

anguspcw
anguspcw

Reputation: 306

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

SELECT is_active, paid,
tags_title = STUFF(
             (SELECT ',' + tags_title FROM table FOR XML PATH ('')), 1, 1, ''
           ) 
FROM table GROUP BY is_active, paid

Upvotes: 0

Related Questions