Make a Crosstab relational?!

Question

Post reply

Make a Crosstab relational?!

chris-322173

Valued Member

Points: 63
More actions
May 18, 2006 at 1:09 pm

#114241

my dilemna is that all of the data that I have to work with is currently stored in sql server in crosstab format, it is accounting data. jan,feb,mar,apr,may, etc all identified by aggregate group (cost center for example). I want to transform this to a more normalized structure so I can archive by date (without having to tag an entire "record") and tag individual fiscalmonths as well as be able to select actual vs forecast data in a less tricky manner. I have stored procedures that populate the actuals monthly and have no problem fixing those to populate a fiscalmonth field as opposed to a field named jan. I am just clueless how to query this into my new format.
How can I go from a crosstab, ie jan, feb, etc to one that just stores an ID for the fiscalmonth and an amount that correlates to the aggregating unit? I am stumped.
tia.

Viewing 5 posts - 1 through 4 (of 4 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Chris,

Without data samples it is going to be difficult.

* Noel

chris-322173 Valued Member Points: 63 More actions · Answer 2

TABLE sample (

costcenter char (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

jan decimal(18, 0) NULL ,

feb decimal(18, 0) NULL ,

mar decimal(18, 0) NULL ,

apr decimal(18, 0) NULL ,

may decimal(18, 0) NULL

)

TABLE samplefm (

fiscalmonthid int NOT NULL ,

fiscalmonth char (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

)

TABLE sampleNew (

costcenter char (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

fiscalmonth int NOT NULL ,

amount decimal(18, 0) NOT NULL

)

Sample is the existing structure, I need to get that data and put into sampleNew. They are just quick examples but if I could just get a sniff how to do it on this scale I could port it for my use.

Assume a row in sample as is would be:

22 5 5 5 5 5

33 7 5 9 2 45

I want to take that and create 5 (actually 1 for each month of the year) new records for each existing row in the sampleNew table using the ID in the fiscal month table that corresponds to each calendar month. Like so:

22 1 5

22 2 5

22 3 5

33 1 7

33 2 5

etc.

and so on.

sorry I excluded the details

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Based on the original posting, the query would be someting like

SELECT AggregateGroup, 1 FiscalMonth, Jan Amount FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 2, Feb FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 3, Mar FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 4, Apr FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 5, May FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 6, Jun FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 7, Jul FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 8, Aug FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 9, Sep FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 10, Oct FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 11, Nov FROM MySourceTable

UNION ALL

SELECT AggregateGroup, 12, Dec FROM MySourceTable

N 56°04'39.16"
E 12°55'05.25"

chris-322173 Valued Member Points: 63 More actions · Answer 4

thanks a million Peter, works perfect and alot simpler than I was envisioning.

-Chris