June 24, 2010 at 10:36 am
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?
June 24, 2010 at 10:38 am
Look at using the PIVOT operator in SQL Server Books Online.
June 24, 2010 at 11:07 am
You can search for cross tab queries as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 24, 2010 at 12:24 pm
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
June 24, 2010 at 2:41 pm
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.
June 24, 2010 at 3:09 pm
Are the SubCategorys the same for every EffDate? Are they consistent across the data?
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply