CODEWITHSUNDEEP
sqlsql-serverpivot
dckuehn
dckuehn

Reputation: 2475

Dynamic pivot with Max(Date) from pivot

I want to keep this general so if it's ok, I'd prefer to not use my specific data set.

My general example: I have a table of customers who have many orders. I want a pivot table that shows how many orders each customer has had in each month (or general time frame). Suppose I want to add a single column that shows the date for that customers most recent order.

So the table will look like:

CustomerID - January - February - March - April - May - MostRecentOrder
      0001 -       2 -        3 -     1 -     1 -   1 -      2013/5/18
      0002 -       1 -        0 -     1 -     0 -   1 -      2013/5/06
      0003 -       0 -        1 -     4 -     1 -   2 -      2013/5/11
      0004 -       2 -        0 -     0 -     1 -   0 -      2013/4/28

I tried to get a working sqlfiddle up at http://sqlfiddle.com/#!6/cbbad/1 but I'm apparently worse than I thought at Dynamic pivots in general.

Upvotes: 1

Views: 1657

Answers (1)

Taryn
Taryn

Reputation: 247720

If you want to include the most recent date for each customer, then you can use max(orderdate) over(partition by customerId):

select customer.CustomerID, 
  o.OrderDate,
  max(o.orderdate) over(partition by customer.customerid) mostrecentorder
from Customers as customer
join Orders as o 
  on customer.CustomerID = o.customerid;

See SQL Fiddle with Demo.

Your full code would be similar to:

declare @start DATE = '2012-01-01';
declare @end DATE = DATEADD(dd,-(DAY(GETDATE())-1),GETDATE());

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

with months (dateList)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,dateList)
    from months
    where DATEADD(month,1,dateList)<=@end
)
select dateList 
into #tempDates
from months
option (maxrecursion 0);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(10), datelist, 120)) 
                    from #tempDates
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '
select *
from
(
  select customer.CustomerID, 
    o.OrderDate,
    max(o.orderdate) over(partition by customer.customerid) mostrecentorder
  from Customers as customer
  join Orders as o 
    on customer.CustomerID = o.customerid
) x
pivot
(
  Count(OrderDate)
  for OrderDate in ('+ @cols + ')
)y'

execute sp_executesql @query

See SQL Fiddle with Demo.

If you want to show the data by month (which you are showing in your result above), then you can alter the code slightly to use DATENAME:

declare @start DATE = '2012-01-01';
declare @end DATE = DATEADD(dd,-(DAY(GETDATE())-1),GETDATE());

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

with months (dateList)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,dateList)
    from months
    where DATEADD(month,1,dateList)<=@end
)
select dateList 
into #tempDates
from months
option (maxrecursion 0);

select @cols = STUFF((SELECT ',' + QUOTENAME(mth) 
                    from
                    (
                      select datepart(month, datelist) so, datename(month, datelist) mth
                      from #tempDates
                    ) d
                    group by so, mth
                    order by so, mth
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '
select customerId, '+@cols+', mostrecentorder
from
(
  select customer.CustomerID, 
    datename(month, o.OrderDate) orderDate,
    max(o.orderdate) over(partition by customer.customerid) mostrecentorder
  from Customers as customer
  join Orders as o 
    on customer.CustomerID = o.customerid
) x
pivot
(
  Count(OrderDate)
  for OrderDate in ('+ @cols + ')
)y'

execute sp_executesql @query;

See SQL Fiddle with Demo. This will give a result:

| CUSTOMERID | JANUARY | FEBRUARY | MARCH | APRIL | MAY | JUNE | JULY | AUGUST | SEPTEMBER | OCTOBER | NOVEMBER | DECEMBER |            MOSTRECENTORDER |
|------------|---------|----------|-------|-------|-----|------|------|--------|-----------|---------|----------|----------|----------------------------|
|          3 |       1 |        1 |     1 |     1 |   1 |    0 |    0 |      0 |         0 |       0 |        0 |        0 | May, 03 2013 00:00:00+0000 |
|          2 |       1 |        1 |     1 |     1 |   1 |    0 |    0 |      0 |         0 |       0 |        0 |        0 | May, 13 2013 00:00:00+0000 |
|          1 |       1 |        2 |     0 |     1 |   1 |    0 |    0 |      0 |         0 |       0 |        0 |        0 | May, 23 2013 00:00:00+0000 |

Upvotes: 2

Related Questions