Pivot the other way

[wyconian]

Hi everyone

I've got a couple of columns that I'm trying to pivot :)

The columns are period (basically date dim with months) and amount.  What I'd like to do is split this into differnt columns so I get jan amount, feb amount, mar amount etc.

Looking at the DM documentaion on pivots I can see that it is possible to do this the other way round i.e. 'pivot' multiple columns into one column, the question is is it possible to do this the other way round i.e. split the columns out?

Currently this is done via if/then/else statements which do the job but the performance is horrible (no surprise there then)

I'd be interested if you have any other ideas on how I can do this if its not possible to pivot it.

BTW I'm using SQL Server 2008 and C10.  There are approx 9mill records

Thanks for your help

[MFGF]

I used to teach Data Manager classes, and this question came up quite often when covering the pivoting features. There are a couple of techniques for "reverse pivoting", originally penned by a colleague many years ago, and I documented these and used to hand out the details below to anyone interested.

https://docs.google.com/open?id=0B6m-USVf1p1nclZLVjNRQWFRSS1QX2c2TlFBZjZwUQ

Regards,

MF.

[wyconian]

MFGF

Thanks for the reply, can't wait to read this.  Unfiortunately my client has blocked google docs.  Is there any way you could mail it to be as an attachment?  My email is removed by the muppet after he sent me the document :).


Thanks a lot.

[MFGF]

Sent! I hope it gives you some ideas.

Cheers!

MF.