November 11, 2014 at 2:58 am
Hi,
I have a table (Event_Table) like:
EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02
And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:
EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11
Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I don´t care)
Is there a simple solution to do this in SQL? 🙂
Thanks!!
BR
Jörgen
November 11, 2014 at 3:20 am
jorgen.olofsson (11/11/2014)
Hi,Is there a simple solution to do this in SQL? 🙂
Thanks!!
BR
Jörgen
Yes, certainly. ROW_NUMBER() + PIVOT
with Event_Table as (
select * from (values
(1, 11, cast('2014-11-11' as date))
,(2, 13, cast('2014-12-10' as date))
,(1, 11, cast('2014-12-21' as date))
,(2, 13, cast('2015-01-11' as date))
,(1, 11, cast('2015-03-02' as date))
) t (EmployeeID, CustomerID, Date)
), nmb as (
select EmployeeID, CustomerID, Date
, dtc = 'Date' + cast(row_number() over(partition by EmployeeID, CustomerID order by Date) as varchar(2))
from Event_Table
)
select EmployeeID, CustomerID, [Date1], [Date2], [Date3]
from nmb
pivot (max(Date) for dtc in ([Date1], [Date2], [Date3])) p
November 11, 2014 at 5:37 am
Ahhh, thanks a lot! Worked perfectly! 🙂
/Jörgen
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply