Pivot tha data in this format

Question

Post reply

Pivot tha data in this format

vkoka

SSC Enthusiast

Points: 190
More actions
April 22, 2009 at 1:40 am

#129605

hi
My data is in this format
jan-2008 feb-2008 mar-2008
Actual 100 200 300
Budget 200 500 800
and i want the result like this
provided the column names and the data.
Actual/Budget Revenue Period/Mon period/Year
Actual 100 jan 2008
Budget 200 jan 2008
Actual 200 Feb 2008
Budget 500 Feb 2008
please do this needful and thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Arun Sathianathan Default port Points: 1442 More actions · Answer 1

Create table forecast (

typevarchar(10),

[jan-2008]int,

[feb-2008]int,

[mar-2008]int )

Insert into forecast values ('Actual', 100, 200, 300)

Insert into forecast values ('Budget', 200, 500, 800)

Select* from forecast

SELECT *

FROMforecast

UNPIVOT (Amount FOR MonthYear IN ([jan-2008],[feb-2008],[mar-2008] )) AS u

arun.sas SSChampion Points: 11831 More actions · Answer 2

Hi,

How can we handle this reversal of pivoting in 2000?

ARUN SAS

Arun Sathianathan Default port Points: 1442 More actions · Answer 3

using a cross join

SELECTa.Type, c.MonthYear,

Sum(CASE WHEN c.MonthYear='jan-2008' THEN a.[jan-2008]

WHEN c.MonthYear='feb-2008' THEN a.[feb-2008]

WHEN c.MonthYear='mar-2008' THEN a.[mar-2008]

END) Amount

FROM forecast a

CROSS JOIN (SELECT 'jan-2008' as MonthYear UNION SELECT 'feb-2008' UNION SELECT 'mar-2008')c

GROUP BY a.Type, c.MonthYear

vkoka SSC Enthusiast Points: 190 More actions · Answer 4

thanks for giving reply. in result i want

-----------------------------------------

actuals/budget | Revenue | Month | year |

------------------------------------------

Actuals |100 |jan |2008 |

BHudget |200 |feb |2008 |

i want month and year in different column. please help me out in this.

Arun Sathianathan Default port Points: 1442 More actions · Answer 5

Use the above query as a subquery and do the conversion in the outer query.

-Arun

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Looking at the schema, and I use the term loosely, of your input table, I assume you are pulling this from a spreadsheet and that you won't have hundreds of thousands of rows being processed. If this is something you are doing a few times a day, and with only a few thousand rows at a time, the following should be adequate for the purpose.

You should probably also hard code a year/month number instead of the characters for month, in order to keep the order of rows correct. Take a look at the orderBY column I'm creating in the code below. If you prefer the order by to be a datetime column so you could join to datetime columns in other tables, just change 200801, to cast('1/1/2008' as datetime). Having separate month and year columns in your tables is generally not a good practice.

Good luck.

declare @sample table (AB char(6), [Jan-2008] int, [Feb-2008] int, [Mar-2008] int)

insert into @sample

select 'Actual', 100, 200, 300 union all

select 'Budget', 200, 500, 800

--set statistics io on;

--set statistics time on;

select AB,[Jan-2008] as revenue,'Jan' as revenueMonth,2008 as revenueYear,200801 as orderBy

from @sample

union all

select AB,[Feb-2008],'Feb',2008,200802

from @sample

union all

select AB,[Mar-2008],'Mar',2008,200803

from @sample

order by orderBy, AB

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

vkoka (4/22/2009)
thanks for giving reply. in result i want
-----------------------------------------
actuals/budget | Revenue | Month | year |
------------------------------------------
Actuals |100 |jan |2008 |
BHudget |200 |feb |2008 |
i want month and year in different column. please help me out in this.

Actually, if you intend to store this in a table, you want year, month, and day all in the same column as a DATETIME datatype. To do otherwise will cause a serious world of pain in the near future because of all the conversions you'll need to make to do other things. Once you've done that, you can use calculated columns for displaying just the year and month without the problems associated with actually storing such things in a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vkoka SSC Enthusiast Points: 190 More actions · Answer 8

vkoka

SSC Enthusiast

Points: 190

April 23, 2009 at 3:51 am

#982734

thanks u very much for u'r guidence..

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Thanks for the feedback. I always wonder if some of these suggestions get through or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)