有没有办法旋转客户 ID 和他们最近的订单日期? [英] Is there a way to pivot a customer ID and a their most recent order dates?

问题描述

我有一个查询，它提供了所有客户及其最近三个订单的日期.

I have a query that gives me all customer's and their last three order dates.

例如:

CustomerId DateOrdered
167 2006-09-16 01:25:38.060
167 2006-09-21 13:11:53.530
171 2006-08-31 15:19:22.543
171 2006-09-01 13:30:54.013
171 2006-09-01 13:34:36.483
178 2006-09-04 11:36:19.983
186 2006-09-05 12:50:27.153
186 2006-09-05 12:51:08.513

我想知道是否有办法让我旋转它以显示如下:

I want to know if there is a way for me to pivot it to display like this:

[CustomerId]    [Most Recent] [Middle] [Oldest]
'167'   '2006-09-21 13:11:53.530'   '2006-09-16 01:25:38.060'   'NULL'
'171'    '2006-09-01 13:34:36.483'   '2006-09-01 13:30:54.013'   '2006-08-31 15:19:22.543'
'178'   '2006-09-04 11:36:19.983'   NULL    NULL
'186'   '2006-09-05 12:51:08.513'   '2006-09-05 12:50:27.153'   NULL    

推荐答案

;WITH YourQuery As
(
SELECT CustomerId, DateOrdered,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY DateOrdered DESC) AS RN
FROM Orders
)
select [CustomerId],
MAX(CASE WHEN RN=1 THEN DateOrdered END) AS  [Most Recent] ,
MAX(CASE WHEN RN=2 THEN DateOrdered END) AS  [Middle] ,
MAX(CASE WHEN RN=3 THEN DateOrdered END) AS  [Oldest] 
FROM YourQuery
WHERE RN<=3
GROUP BY [CustomerId]

这篇关于有没有办法旋转客户 ID 和他们最近的订单日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！