De-normalizing Data

Question

Post reply

De-normalizing Data

Nilssond

Hall of Fame

Points: 3285
More actions
June 24, 2010 at 10:36 am

#234246

I have a table with the following data:
SeqNbr Value EffDate SubCategory Category
1 0.00 2090101 A 100
1 15.00 20100101 A 100
1 15.00 20090101 B 100
1 0.00 20100101 B 100
1 0.00 20090101 C 100
1 0.00 20100101 C 100
And i need to transform this data into the following format:
Category EffDate SubCatAValue SubCatBCalue SubCatCValue
100 20090101 0.00 15.00 0.00
100 20100101 15.00 0.00 0.00
Any SQL which can perform this?

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

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Look at using the PIVOT operator in SQL Server Books Online.

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736193

June 24, 2010 at 11:07 am

#1184625

You can search for cross tab queries as well.

Nilssond Hall of Fame Points: 3285 More actions · Answer 3

I do not need to aggregate any data, just display the data in historical order. This issue is that each subcategory may have more detail lines than the other, but I need to keep each line distinct by effective date, regardless of whether or not a particular subcategory has an effective date that matches

LutzM SSC Guru Points: 107049 More actions · Answer 4

Nilssond (6/24/2010)
I do not need to aggregate any data, just display the data in historical order. This issue is that each subcategory may have more detail lines than the other, but I need to keep each line distinct by effective date, regardless of whether or not a particular subcategory has an effective date that matches

That's exactly what you'll get when using either PIVOT or CrossTab (see the rlated link in my signature for an example how to do it).

If you need further assistance please provide table def and sample data in a ready to use format as described in the first link in my signature.

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]

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 5

Are the SubCategorys the same for every EffDate? Are they consistent across the data?

Scott Pletcher, SQL Server MVP 2008-2010