sql server pivot using date to order data in columns

Question

My tables combined look something like:

Seller_ID |From        |To            |Depart_Date            |Arrival_Date  
-------------------------------------------------------------------------------------
Paul      |office      |client_23     |10/20/2015 3:30:00 PM  |10/21/2015 7:54:00 AM
Paul      |client_23   |client_ 88fe  |10/21/2015 11:55:00 AM |10/22/2015 8:11:00 PM
Paul      |client_88fe |client_avr4   |10/23/2015 3:57:00 PM  |10/26/2015 11:27:00 AM
Paul      |client_avr4 |home          |10/26/2015 5:28:00 PM  |10/28/2015 3:39:00 PM

I do not have a indicator like first visit, second visit, third visit...
First visit always has 'From' = office and last visit always has 'To' = home.
Only way to make the sequence is either go backwards by Dates (or by From-To ??)

My desired outcome would be :

|Seller_ID  |from     |office_departure      |client1      |clt1_arrival         |clt1_departure            |client2     |clt2_arrival           |clt2_departure         |client3  |clt3_arrival                |clt3_departure          |home_arrival
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Paul       |office   |10/20/2015 3:30:00PM  |client_23    |10/21/2015 7:54:00 AM|10/21/2015 11:55:00 AM    |client_ 88fe|10/22/2015 8:11:00 PM  |10/23/2015 3:57:00 PM  |client_avr4  |10/26/2015 11:27:00 AM  |10/26/2015 5:28:00 PM   |10/26/2015 5:28:00 PM

Any help is apreciated! thanks in advance.

What have you tried? Was your Pivot/Unpivot unsuccesful or do yo usimply not understand the syntax? — Julien Blanchard, Nov 24 '15 at 16:20

score 0 · Answer 1 · answered Nov 24 '15 at 16:28

If your case is just one trip per seller, or that you have another field not shown here that you can use in group by, you can use something like this:

select
  Seller_ID,
  max(case when RN = 1 then [From] end),
  max(case when RN = 1 then [Depart_Date] end),
  max(case when RN = 1 then [Arrival_Date] end),
  max(case when RN = 2 then [From] end),
  max(case when RN = 2 then [Depart_Date] end),
  max(case when RN = 2 then [Arrival_Date] end)
from (
  select 
    row_number() over (partition by Seller_ID 
                       order by Depart_Date asc) as RN,
    *
  from Table1
) X
group by Seller_ID

Result:

Seller_ID                       
Paul    office  October, 20 2015 15:30:00   October, 21 2015 07:54:00   client_23   October, 21 2015 11:55:00   October, 22 2015 20:11:00

Example in SQL Fiddle

If you don't have any field for tracking the number of trip, you can use running total in order of departure date for statement case when [From] = 'office' then 1 else 0 end partitioned by Seller_ID, that will then assign trip number for the rows.

sorry guys just too many hours looking at code and i was just not seeing it ! thanks JamesZ works perfectly — ruisadias, Nov 25 '15 at 09:58

sql server pivot using date to order data in columns

1 Answers1