0

I am using sql server 2014

I wanted to get Id (identity) field for maximum date as per order status id

Customer - Order Table:

Id (identity) | Customer ID | Order Status ID | Order Date

10000     |   11        |   1             |  1 Feb 2017
10001     |   11        |   2             |  2 Feb 2017
10002     |   11        |   2             |  3 Feb 2017
10003     |   11        |   3             |  4 Feb 2017
10004     |   11        |   3             |  5 Feb 2017

Expected Result

Customer ID |     1 |    2 |     3
11          | 10000 |10002 | 10004           
  • You need to state (in the question tags) which database engine you are using. Some support PIVOT/UNPIVOT directly, some have equivalent functions as extensions, and some require you to perform the process more manually. See stackoverflow.com/tags/pivot/info – David Spillett Feb 15 '18 at 10:25
  • I am using sql server 2014 – user142631 Feb 15 '18 at 10:29
  • Has Order Status ID a well-known different values? f.e. 1,2,3 or is it variable. – McNets Feb 15 '18 at 11:31
  • These are status id for order - 1:order request 2:order confirm 3:order shipped.There multiple entries as order confirm by 2 level of hierarchy in seller – user142631 Feb 15 '18 at 11:48
0

Look for this solution:

SELECT *
FROM
(
    SELECT [Customer ID], [Order Status ID], rmax = MAX(Id)
    FROM OrderTable
    GROUP BY [Customer ID], [Order Status ID]
) A
PIVOT 
(
    MAX(rmax) FOR
    [Order Status ID] IN ([1],[2],[3])
) A_pvt

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.