CODEWITHSUNDEEP
sqlsql-server
You
You

Reputation: 47

Merge results to one column

I have the following query: 

SELECT  a.User1 as Employee
        , isnull(sum(distinct b.Page_Count),0) AS Yesterday
        , isnull(sum(distinct c.Page_Count),0) AS Today
        , isnull(sum(distinct d.Page_Count),0) AS Week
        , e.Material_Location as '(Yesterday)'
        , f.Material_Location as '(Today)'
From    TaskUser AS a
    LEFT JOIN PaperMaterial AS b
    ON b.Assigned_To = a.User1
    AND b.Date_Assigned between ('06/09/2014') AND ('06/13/2014')
    LEFT JOIN PaperMaterial AS c
    ON c.Assigned_To = a.User1
    AND c.Date_Assigned between ('06/13/2014') AND ('06/14/2014')
    LEFT JOIN PaperMaterial AS d
    ON d.Assigned_To = a.User1
    AND d.Date_Assigned between ('06/09/2014') AND ('06/14/2014')
    LEFT JOIN PaperMaterial AS e
    ON e.Assigned_To = a.User1
    AND e.Date_Assigned between ('06/12/2014') AND ('06/13/2014')
    LEFT JOIN PaperMaterial AS f
    ON f.Assigned_To = a.User1
    AND f.Date_Assigned between ('06/13/2014') AND ('06/14/2014')
GROUP BY a.User1, e.Material_Location, f.Material_Location
Order By a.User1, e.Material_Location, f.Material_Location

If multiple records were input for the same user on the same day, I am getting unique rows for the same person. I only want one row per user with the e and f results merged to the same column.

Ie: Current Output = 

Amy   0   640   640   NoTask   Task  
Amy   0   640   640   Task2    Task  
Amy   0   640   640   Task3    Task4  
Amy   0   640   640   Task1    NoTask

Requested output: 

Amy   0   640   640   (NoTask, Task1, Task2, Task3) (NoTask, Task, Task4)

Upvotes: 0

Views: 77

Answers (1)

Andrew
Andrew

Reputation: 8758

Here's a greatly over-simplified example of using stuff combined with a correlated subquery: SQL Fiddle

I used your output as a table, more or less:

select
name,
 stuff(
    (
    select cast(',' as varchar(max)) + mt.one
    from MyTable mt
      WHERE mt.name = t1.name
    order by mt.name
    for xml path('')
    ), 1, 1, '') 
from mytable t1
group by name

We're using stuff to concatenate each value for the column I creatively named ONE for each NAME. The correlated subquery allows us to relate each row coming out of that to the corresponding row coming out of the main query.

Upvotes: 1

Related Questions