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
Improve this question
4
  • 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, 2018 at 10:25
  • I am using sql server 2014
    – user142631
    Feb 15, 2018 at 10:29
  • Has Order Status ID a well-known different values? f.e. 1,2,3 or is it variable.
    – McNets
    Feb 15, 2018 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, 2018 at 11:48

1 Answer 1

Reset to default
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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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