October 31, 2006 at 11:33 am
I would like to create a table or a view where one row will represent data from multiple tables, including Parent-child tables.
Specifically each person in our system can have multiple IDs, such as in
Master
Child
And I need to create 1 row to represent this, as in
Person1, ID1, ID2, NULL
Person2, ID1, NULL, ID3
I can create the new columns using case statements, but it's the table relationships that throw me off so that I get many rows. I realize this may be basically a join problem, but I'm not sure. In reality, there are multiple Masters and multiple Childs that I have to deal with.
Is this possible? Any help would be greatly appreciated.
Thanks, Arthur
October 31, 2006 at 2:04 pm
Are you trying to get a row for each person with all id's. Basically de-normalize and have a list personid, id1, id2, id3....?
If so then you are talking
child
1. person1, id1
2. person1, id2
3. person2, id3
4. person2, id1
view
personid, id1, id2, id3, id4...?
October 31, 2006 at 2:17 pm
The view you describe is what I want, but I can't denormalize, unless I do that for a temp table.
I guess I'm looking to create a pivot table. And at this point I'm thinking my only option is to have multiple inserts based on several queries. UNLESS there is something with a cursor or dynamic SQL that I can do, or something else I haven't thought of. I'm doing all INNER JOINS, and with CASE statements to generate the new columns, but I'm getting a very large row count.
October 31, 2006 at 2:51 pm
This is a stab at it. But I'm sure I am missing a rule...
create view v_yada
as
select personid, chd1.id, chd2.id
from master
left join
(select personid, id from child where id = 1) chd1
ON master.person = chd1.person
left join
(select personid, id from child where id = 2) chd2
ON master.person = chd2.person
...
October 31, 2006 at 2:53 pm
This is a classic case of vertical to horizontal rotation. There is no general solution for this using sql. You need to put limits around this problem, i.e. how many possible IDx values are there?
In real life, this could be a gathering of data representing people's first and second choice of something at different moments in time. So you want to see all the choices each person ever made.
You'll need to first convert this into a single choice view with something like
select personid,choice1 as choice from thetable
union
select personid,choice2 as choice from thetable
You then need to apply case syntax to convert this into a horizontal list
select personid,
case when choice='CH1' then choice else null end ch1
case when choice='CH2' then choice else null end ch2
etc.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply