Pivot with Sub Pivot Problem

Question

Post reply

Pivot with Sub Pivot Problem

Northern Monkey

Ten Centuries

Points: 1017
More actions
April 30, 2010 at 3:21 am

#234466

Hi All
I had created this in Crystal reports 2008 but can't export it to excel so I am trying to create this in SQL 2008
What I need to do is this:-
Create a single row from data with a pivot of the following data example
Unit_ID ¦ Date_Field ¦ Unit_Type ¦ Budget_Value ¦ Forecast_Value ¦ Ect Ect
1234 ¦ 2007-04-01 ¦ typ1 ¦ 350.00 ¦ 200.00 ¦
1234 ¦ 2007-05-01 ¦ typ1 ¦ 450.00 ¦ 230.00 ¦
1234 ¦ 2007-06-01 ¦ typ1 ¦ 550.00 ¦ 240.00 ¦
1234 ¦ 2007-04-01 ¦ typ2 ¦ 250.00 ¦ 270.00 ¦
1234 ¦ 2007-05-01 ¦ typ2 ¦ 850.00 ¦ 700.00 ¦
1234 ¦ 2007-06-01 ¦ typ2 ¦ 450.00 ¦ 290.00 ¦
2345 ¦ 2007-04-01 ¦ typ1 ¦ 350.00 ¦ 200.00 ¦
2345 ¦ 2007-05-01 ¦ typ1 ¦ 450.00 ¦ 230.00 ¦
2345 ¦ 2007-06-01 ¦ typ1 ¦ 550.00 ¦ 240.00 ¦
2345 ¦ 2007-04-01 ¦ typ2 ¦ 250.00 ¦ 270.00 ¦
2345 ¦ 2007-05-01 ¦ typ2 ¦ 850.00 ¦ 700.00 ¦
2345 ¦ 2007-06-01 ¦ typ2 ¦ 450.00 ¦ 290.00 ¦
I need to create one single row containging the data grouping the dates into Qtrs with the unit_type then sum the budget and forecast values ie like this
NOTE Qtr 1 below inc 2007-04-01, 2007-05-01, 2007-06-01 they are not set to SQL deft Qtrs
Unit_ID ¦ 2007_Qtr1_Type_1 ¦ 2007_Qtr1_Type_2
1234 ¦ BV Sum 1350.00 ¦ FV Sum 670.00 ¦ BV Sum 1550.00 FV ¦ Sum 1260.00
2345 ¦ BV Sum 1350.00 ¦ FV Sum 670.00 ¦ BV Sum 1550.00 FV ¦ Sum 1260.00
Not sure if this can be done??
The tsql i have so far is this but I can only get one SUM?
SELECT *
FROM
(SELECT investment_accountcode, CAST(DATEPART(year,[fiscal_period_start]) As varchar(4)) + '_' + RIGHT('0' + CAST(DATEPART(month,[fiscal_period_start]) As varchar(2)), 2) as themonth, investment_actual_benefit
FROM trdm_investstructure AS trdm_investstructure LEFT OUTER JOIN
trdm_investbenefitrollups AS trdm_investbenefitrollups ON
trdm_investstructure.investment_id = trdm_investbenefitrollups.investment_id LEFT OUTER JOIN
trdm_strategic_pri_invest AS trdm_strategic_pri_invest ON trdm_investstructure.investment_id = trdm_strategic_pri_invest.invest_id LEFT OUTER JOIN
trdm_investaccess AS trdm_investaccess ON trdm_investstructure.investment_id = trdm_investaccess.investment_id
WHERE fiscal_period_start between '4/1/2007' and '03/31/2010' ) p
PIVOT (SUM(p.investment_actual_benefit)FOR themonth in ([2007_04],[2007_05],[2007_06],[2008_04],[2008_05],[2008_06],[2009_04],[2009_05],[2009_06],[2010_04],[2010_05],[2010_06])) as pvt
I also need to know if the FOR IN statment can be dinamic from the data or a wild card?
Tks in advance to what I think is a very hard bit of coding where I am out of my depth in SQL but have the data in Crystal but can't do any thing with it LOL as the system can't export the data to any file format.....

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at the CrossTab article as well as the DynamicCrossTab article referenced in my signature.

This will most definitely help you to solve this issue.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

The two articles that Lutz points out above will show you how to do the "multi-pivot" you seek... and all the reasons not to use it as well as an alternative that you should use.

--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)