populating a table based on another table column values

Question

populating a table based on another table column values

arvind.periyasamy

Newbie

Points: 6
More actions
August 16, 2012 at 12:10 pm

#279842

I have an object table:
I have another table 'ObjectValue'
Output: I'm trying to create a new table with this format;
OutTable:
my objectvalue table is really huge, so thought doing a pivot or unipivot is not a good practice. Any help/idea is much appriciated.
thank you.
Arv

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

You can't avoid a pivot here (that's exactly what this is), you're going to do that or cross-tab, which is a series of grouping functions with your pivot pre-built, basically.

Out of curiousity, what front end or delivery system are you going to end up using to give this translated data to an end user? It may be a lot easier to perform the pivot in that system.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

arvind.periyasamy Newbie Points: 6 More actions · Answer 2

Thanks for your reply.

I'm planning to build this output table for me to use in reporting queries. Without this flat table, each time I'm going to the individual tables and doing a join to know the value. As I said since my data is huge (expecting 5 million rows per month in the object values table), I thought flattening this table and using would be a good idea.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Yeah, preflattening might be a best choice here, but somewhere in your ETL you'll end up doing a pivot somewhere. Have you looked into SSAS and attaching reports to that? For your data volume it might be a good choice but it depends on how much time you have to ramp up on a technology.

The real pain, for me anyways, was when I'd pre-pivot I'd then have to create the mother of all change cascade scripts to deal with old data finally arriving and needing to be included, other data being removed and thus needs to be pulled out of the pivot, etc etc. It works once it's done but getting there... ugh, yeah, not fun.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA