How to unite multiple rows in pivot columns

Question

I have a table like this:

Employee    Month       ActDate
Emp 1       Feb     10/02/2019
Emp 1       Feb     15/02/2019
Emp 1       Mar     10/03/2019
Emp 2       Mar     09/03/2019
Emp 2       Apr     04/04/2019
Emp 2       Apr     05/04/2019
Emp 3       Feb     03/02/2019
Emp 4       Feb     06/02/2019

and I need to make it look like this

Employee    Feb                         Mar            Apr
Emp 1       10/02/2019  -  15/02/2019   10/03/2019  
Emp 2                                   09/03/2019     04/04/2019  -  05/04/2019
Emp 3       03/02/2019      
Emp 4       06/02/2019

I can pivot this but for example Emp2 would have 2 rows on Feb, i need to concatenate all rows for the same employee, how can i achieve this?

Using SQL Server 2008

Thanks

Have you tried anything? There are plenty of questions of how to pivot data on Stack Overflow, why didn't those help you? I would personally suggest having 2 column for the dates as well, not 1 (a From and To date). — Thom A, Dec 06 '19 at 12:53
Does [this](https://stackoverflow.com/q/15931607/3484879r) answer your question? Perhaps [this](https://stackoverflow.com/q/10428993/3484879), or [this](https://stackoverflow.com/q/15745042/3484879), or any of the other results [here](https://www.google.com/search?q=site%3Astackoverflow.com+How+to+pivot+data+in+SQL+Server)? — Thom A, Dec 06 '19 at 12:56
Hello Larnu, the problem isn't the pivot, it's to not generate mora than one row per employee, i have multiple dates for the same emp in the same month, so two columns like a from - to won't work as well, if emp 1 has 5 dates in Feb, I need all the 5 dates in the same cell — Iago Losada Alibune, Dec 06 '19 at 13:19
Then the pivot is wrong. If you show is your code we can explain why. — Thom A, Dec 06 '19 at 13:31

score 2 · Accepted Answer · answered Dec 06 '19 at 12:57

This is aggregation with some conditional logic. This might be simplest with two levels of aggregation:

select employee,
       (case when mon = 'Feb' then max(val) end) as Feb,
       (case when mon = 'Mar' then max(val) end) as Mar,
       (case when mon = 'Apr' then max(val) end) as Apr
from (select employee, mon,
             (case when min(actdate) = max(actdate)
                   then convert(varchar(255), min(actdate))
                   else convert(varchar(255), min(actdate)) + ' - ' + convert(varchar(255), max(actdate))
              end) as val
      from t
      group by employee, mon
     ) em
group by employee;

How to unite multiple rows in pivot columns

1 Answers1