Pivot Table in AX

Question

I came across a problem in AX 2009 and I have to admit I'm basically clueless.

I would like to build a view (based on an AOT query or not; but from what I understand you can do more using an AOT query than strictly with a view) using a table which looks like this:

id    status    date
1     IN        10/10/2011
1     OUT       11/10/2011
2     OUT       12/10/2011
3     IN        13/10/2011
4     IN        14/10/2011
4     OUT       15/10/2011

The view would ideally look like this:

id   IN_Date      OUT_Date  
1    10/10/2011   11/10/2011
2    *NULL*       12/10/2011
3    13/10/2011   *NULL*
4    14/10/2011   15/10/2011

In strict SQL or even using Microsoft Access it's a trivial task to do but I can't find a way to do it in AX 2009 since there is no "AS" option in views' fields. I don't want to use display methods since I want the view to be accessed from outside of AX. Any hint would be greatly appreciated!

Answer 1

You would like to do a full outer join on the table (joined with itself).

This is not supported in X++ or as a query joinMode, but can be simulated using two intermediate views with outer join combined with a union.

View 1:

select id, date as dateIn from table as table1 
    outer join date as dateOut from table as table2
    where table2.id == table1.id and
          table1.status == Status::In and
          table2.status == Status::Out

View 2:

select id, date as dateOut from table as table1 
    outer join date as dateIn from table as table2
    where table2.id == table1.id and
          table1.status == Status::Out and
          table2.status == Status::In

View 3:

select id, dateIn, dateOut from view1 
union 
select id, dateIn, dateOut from view2

The above is more or less SQL, which can be turned into AX queries and views.

A way to do that is given in this answer.