Cross table issue

[alec1982]

Hi guys,

I have a table that looks like

ID     2011     2012     2013

1     10          12          14

1     12          14          20

1     30           20         50

2     3            10          15

2     25           25          3

3     24          21           35

How can I do cross table so I get :

ID     Amount

1          36

1          46

1          100

2          28

2          53

3          80

Thanks in advance for your help!

[Anonymous · Accepted solution]

Shouldn't be a problem - but it is not clear what is the meaning of three rows for ID 1, and two rows for ID 2.  If there is no meaning, the result should be:

1     182

2     81

3     80

Need clarification

Regards,

Michael

Edit:  I just thought that you maybe have a unique row id (?).  If this is the case - see attched.

[alec1982]

Thanks man,

I still have one little thing.

How can I keep a year field to show the year

So it will look like:

RawID      ID     Year     Sum(Amount)

Thanks,

Alec,

[manojkvrajan]

Please add the Year field to the final script right before the Sum (Amount) and add the Year to the Group By clause.

[alec1982]

Thanks!