3

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;
Community
  • 1
  • 1
sunleo
  • 10,589
  • 35
  • 116
  • 196
  • When LEFT JOIN, put the right side table's conditions in the ON clause to get true left join behavior. (When in WHERE, you get regular inner join result.) – jarlh Jan 24 '17 at 08:46
  • @jarlh: well, that seems to be his database schema, the other two tables are needed to get to the last two – Tim Schmelter Jan 24 '17 at 08:49
  • 1
    @TimSchmelter, of course... Thanks! Seems like I need another cup of coffee... – jarlh Jan 24 '17 at 08:51

3 Answers3

2

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
Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
  • Did you removed the Left Join which I removed above? I think there is no way to display the result as first. @sunleo – Shakeer Mirza Jan 24 '17 at 09:22
  • Paste it in your question itself @sunleo – Shakeer Mirza Jan 24 '17 at 09:25
  • but I have that column in group by and select so it should work right, I can have any no of column only single column(tags_title) is for stuff?,This is not issue what you mentioned as update. – sunleo Jan 24 '17 at 09:40
  • Thanks, got it, could you tell me how to solve this issue because extra column is creating distinct record. – sunleo Jan 24 '17 at 09:55
  • @sunleo Remove Whole Group By from your query. If there is no aggregate function in select, there is no meaning of grouping. So here Distinct and Group by doing the same. And you mentioned tags_title in Group By. This wont give you correct result – Shakeer Mirza Jan 24 '17 at 10:03
0

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
anguspcw
  • 306
  • 2
  • 18
0

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;
McNets
  • 10,352
  • 3
  • 32
  • 61