SQL Server Pivots... Can I show the last five orders for a series of order types

Question

I have the following table:

create table myorders(ordertype char(1), orderdate datetime, orderid int)

This table has the following data:

insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 20:04:48:287', 11082360)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 20:02:40:407', 40087130)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 20:02:07:277', 7990558)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:58:46:097', 8225181)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:58:39:740', 40087129)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:57:33:063', 8225235)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:56:17:207', 8225233)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:54:50:630', 8225232)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:48:07:300', 11082337)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:47:49:997', 40087128)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:46:40:667', 40087127)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:45:32:550', 8225231)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:45:11:203', 11082326)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:44:57:990', 8225230)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:43:52:953', 40087126)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:43:20:853', 8225229)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:41:33:740', 11082319)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:41:19:853', 8225228)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:40:33:127', 40087125)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:40:25:537', 40087124)

The data looks like this:

OrderType  OrderDate                    OrderId
--------   -----------------------      ---------
P      2013-02-14 20:04:48.287  11082360
P      2013-02-14 20:02:40.407  40087130
P      2013-02-14 20:02:07.277  7990558
C      2013-02-14 19:58:46.097  8225181
P      2013-02-14 19:58:39.740  40087129
C      2013-02-14 19:57:33.063  8225235
C      2013-02-14 19:56:17.207  8225233
C      2013-02-14 19:54:50.630  8225232
P      2013-02-14 19:48:07.300  11082337
P      2013-02-14 19:47:49.997  40087128
P      2013-02-14 19:46:40.667  40087127
C      2013-02-14 19:45:32.550  8225231
P      2013-02-14 19:45:11.203  11082326
C      2013-02-14 19:44:57.990  8225230
P      2013-02-14 19:43:52.953  40087126
C          2013-02-14 19:43:20.853  8225229
P      2013-02-14 19:41:33.740  11082319
C      2013-02-14 19:41:19.853  8225228
P      2013-02-14 19:40:33.127  40087125
P      2013-02-14 19:40:25.537  40087124

I would like to pivot the data so it looks like this:

C_LastFiveOrders  C_OrderDate                  P_LastFiveOrders P_OrderDate
----------------  -----------------------      ---------------- -----------------------
8225181           2013-02-14 19:58:46.097      11082360         2013-02-14 20:04:48.287
8225235           2013-02-14 19:57:33.063      40087130         2013-02-14 20:02:40.407
8225233           2013-02-14 19:56:17.207      7990558          2013-02-14 20:02:07.277
8225232           2013-02-14 19:54:50.630      40087129         2013-02-14 19:58:39.740
8225231           2013-02-14 19:45:32.550      11082337         2013-02-14 19:48:07.300

Notice that the orders are sorted by orderdate in decending order.

I would like to be able to add additional OrderTypes. I would also like the flexibility to look at last x orders. In the sample I'm looking at the last five orders. I would like to be able to look at the last 10 or 20 orders.

Answer 1

There are several different ways that this can be performed.

Aggregate with CASE: You can use an aggregate function with a CASE expression:

select 
  max(case when ordertype = 'c' then orderid end) C_LASTFIVEORDERS,
  max(case when ordertype = 'c' then orderdate end) C_ORDERDATE,
  max(case when ordertype = 'p' then orderid end) P_LASTFIVEORDERS,
  max(case when ordertype = 'p' then orderdate end) P_ORDERDATE
from
(
  select orderid, ordertype, orderdate, rn
  from
  (
    select orderid, ordertype, orderdate,
      row_number() over(partition by ordertype
                        order by orderdate desc) rn
    from myorders
  ) src
  where rn <=5
) s
group by rn

See SQL Fiddle with Demo.

Multiple Joins: You can join on your table multiple times:

;with cte as
(
  select orderid, ordertype, orderdate,
      row_number() over(partition by ordertype
                        order by orderdate desc) rn
  from myorders
)
select 
  c1.orderid C_LASTFIVEORDERS,
  c1.orderdate C_ORDERDATE,
  c2.orderid P_LASTFIVEORDERS,
  c2.orderdate P_ORDERDATE
from cte c1
left join cte c2
  on c1.rn = c2.rn
  and c2.ordertype = 'P'
where c1.rn <=5
  and c1.ordertype = 'C'

See SQL Fiddle with Demo

Static PIVOT:

Lastly, you can apply both the UNPIVOT and the PIVOT function to get the result:

select C_LastFiveOrders, C_orderdate,
  P_LastFiveOrders, P_orderdate
from
(
  select rn,
    case 
      when col = 'orderid' then ordertype+'_LastFiveOrders'
      else ordertype+'_'+col end col_name,
    value
  from
  (
    select ordertype,
      convert(varchar(50), orderdate, 121) orderdate,
      cast(orderid as varchar(50)) orderid,
      row_number() over(partition by ordertype 
                        order by orderdate desc) rn
    from myorders 
  )src
  unpivot
  (
    value
    for col in (orderdate, orderid)
  ) un
  where rn <= 5
) s
pivot
(
  max(value)
  for col_name in (C_LastFiveOrders, C_orderdate,
                   P_LastFiveOrders, P_orderdate) 
) piv

See SQL Fiddle with Demo.

Dynamic PIVOT:

If your OrderType values are unknown, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @colsName AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ordertype +c.col) 
                      from myorders
                      cross apply 
                      (
                        select '_LastFiveOrders' col
                        union all
                        select '_OrderDate'
                      ) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'SELECT ' + @cols + ' 
     from 
     (
      select rn,
        case 
          when col = ''orderid'' then ordertype+''_LastFiveOrders''
          else ordertype+''_''+col end col_name,
        value
      from
      (
        select ordertype,
          convert(varchar(50), orderdate, 121) orderdate,
          cast(orderid as varchar(50)) orderid,
          row_number() over(partition by ordertype 
                            order by orderdate desc) rn
        from myorders 
      )src
      unpivot
      (
        value
        for col in (orderdate, orderid)
      ) un
      where rn <= 5
     ) s
     pivot 
     (
       max(value)
       for col_name in (' + @cols + ')
     ) p 
     '

execute(@query)

See SQL Fiddle with Demo

All queries give the result:

| C_LASTFIVEORDERS |             C_ORDERDATE | P_LASTFIVEORDERS |             P_ORDERDATE |
-------------------------------------------------------------------------------------------
|          8225181 | 2013-02-14 19:58:46.097 |         11082360 | 2013-02-14 20:04:48.287 |
|          8225235 | 2013-02-14 19:57:33.063 |         40087130 | 2013-02-14 20:02:40.407 |
|          8225233 | 2013-02-14 19:56:17.207 |          7990558 | 2013-02-14 20:02:07.277 |
|          8225232 | 2013-02-14 19:54:50.630 |         40087129 | 2013-02-14 19:58:39.740 |
|          8225231 | 2013-02-14 19:45:32.550 |         11082337 | 2013-02-14 19:48:07.300 |

Answer 2

This may work:

;WITH myorders_CTE AS
  (
  SELECT ordertype, orderdate, orderid,
    row_number() over (partition BY ordertype ORDER BY orderdate DESC) AS sequence
  FROM myorders
  )

SELECT c.orderid AS C_LastFiveOrders, c.orderdate AS C_OrderDate,
  p.orderid AS P_LastFiveOrders, p.orderdate AS P_OrderDate
FROM myorders_CTE c
JOIN myorders_CTE p ON c.sequence = p.sequence
WHERE c.ordertype = 'C'
  AND p.ordertype = 'P'
  AND c.sequence <= 5
  AND p.sequence <= 5
ORDER BY c.sequence

It uses a self-join, but doesn't use PIVOT.