April 22, 2009 at 1:40 am
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.
April 22, 2009 at 4:16 am
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
April 22, 2009 at 4:36 am
Hi,
How can we handle this reversal of pivoting in 2000?
ARUN SAS
April 22, 2009 at 4:50 am
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
April 22, 2009 at 4:59 am
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.
April 22, 2009 at 6:44 am
Use the above query as a subquery and do the conversion in the outer query.
-Arun
April 22, 2009 at 9:20 am
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
April 22, 2009 at 11:56 am
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
Change is inevitable... Change for the better is not.
April 23, 2009 at 3:51 am
thanks u very much for u'r guidence..
April 23, 2009 at 5:32 pm
Thanks for the feedback. I always wonder if some of these suggestions get through or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply