I have been struggling to figure out what should be a very simple task - transposing rows to columns, either in t-sql or preferrably in Visual Studio. I don't think this can be done with commands like PIVOT or UNPIVOT.
Basically what I am trying to do is the following. I have a table with rows in the following format:
CustomerSegment Jan2008Rev Feb2008Rev Mar2008Rev ....etc
Male0_12 400,000.00 300,000.00 350,000.00
Male12_18 600,000.00 500,000.00 700,000.00
Female0_12 700,000.00 650,000.00 450,000.00
Female12_18 800,000.00 600,000.00 525,000.00
...and I want to create a report that ends up printing like the following:
Male0_12 Male12_18 Female0_12 Female12_18
Jan2008Rev 400,000.00 600,000.00 700,000.00 800,000.00
Feb2008Rev 300,000.00 500,000.00 650,000.00 600,000.00
Mar2008Rev 350,000.00 700,000.00 450,000.00 525,000.00
That's it - simply transposing rows to columns. Just like if I were in Excel and copied a block of data and used 'paste special' and selected the 'Transpose' option.
I have tried PIVOT and UNPIVOT in a T-SQL query and played around with matrix options in Visual Studio and have been unable to solve what seems like a very simple problem.
Thanks very much in advance!