December 16, 2009 at 1:38 pm
Hi guys
I am a BI developer with elementary sql skills. i have an interesting task to "flatten" a recursive table that has multple pk/fk relationships in order to complete the parent/child relationship. Here's how it goes:
the table structure looks like this, its an example i drummed up but mirrors the business req exactly:
[Content] has many [Paragraph],[Sentences], [Words]
[Paragraph] has many [Sentences], [Words]
[Sentences] has many [Words].
[Content] has pkid which contains ids for all the objects.
You only content-paragraph relationship by looking at [Content].pkid = [Paragraph].fkid
You only paragraph-sentence relationship by looking at [Paragraph].pkid = [Sentence].fkid
The idea is to generate a table looking like this
ParentId, ChildId, Type, Qty
-------------------------------
content1, paragraph1, Content, 1
content1, word1, Content, 2
Paragraph1, Sentence1, Paragraph, 2
Paragraph1, Sentence2, Paragraph, 1
Since this requires a bunch of pk/fk relationships to figure out the parent/child relationship would this be built/maintained better in a CTE expression (hints on how to construct?) or in a SSIS package?
Thanks a lot of for your input and suggestions.
December 16, 2009 at 1:45 pm
Can you provide the table definitions and some sample data for the current tables?
From what you're describing, it seems like the whole thing could be simply a few joins and unions to get all your data, and wouldn't require anything complex or "clever" at all. I'd need to see table structures to be sure of that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply