Pivoting in T-SQL based on date offset

Question

There is a table called Orders has three columns:

• OrderID

• UserID

• OrderDate

I need to know, for X months in the past, A users placed 1 order, B users placed 2 orders, C users placed 3 orders, etc.

So the output is a table w/ a MinDate field and then columns for each # of orders from 1 to N, e.g.:

declare @t table ( 
  MinDate datetime,
  o_1 int,
  o_2 int,
  o_3 int,
  o_4 int,
  ...
  o_N int
)

Each field contains the number of unique customers (UserID's) that placed that many orders between MinDate and the current date. For example, if a row has MinDate = '2009-09-01' then o_6 contains the number of customers who have placed 6 orders between Sept 1 and present, etc.

I have tried several approaches, LEFT JOIN Orders to a number table, etc. but am having trouble getting the correct count of unique users into each column. I could do this easily (and slowly) with a CURSOR but that seems like cheating.

Any suggestions? Let me know if my explanation does not make sense...

Answer 1

Here's a stab at it; the part where it breaks down for me is the dynamic pivoting (in other words, you could have an infinite range of orders 1-N). However, it may point you (or someone else) in the general direction.

DECLARE @Orders TABLE
    (
      OrderID INT ,
      UserID INT ,
      OrderDate SMALLDATETIME
    )

INSERT  INTO @Orders
        ( OrderID, UserID, OrderDate )
VALUES  ( 1, 1, '20090102' ),
        ( 2, 1, '20090102' ),
        ( 3, 2, '20090102' ),
        ( 4, 2, '20090102' ),
        ( 5, 2, '20090102' ),
        ( 6, 3, '20090102' ),
        ( 7, 4, '20090102' )

SELECT  *
FROM    ( SELECT    UserID ,
                    COUNT(OrderID) AS CountOrders
          FROM      @Orders o
          WHERE     OrderDate > '20090101'
          GROUP BY  UserID
        ) x PIVOT ( COUNT(UserID) FOR CountOrders IN ( [1], [2], [3], [4] ) ) AS p

Answer 2

http://www.codeproject.com/KB/database/Pivot2Columns.aspx?display=Print