Denormalization Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Denormalization Help

Author

Topic

NgKH
Starting Member

15 Posts

Posted - 2003-04-21 : 17:10:23

how would i denormalize my person table from structure

Person Table (person_id, household_id)
e.g.
person_id household_id
1 1
2 1
3 1
4 1
5 1
6 1
7 2
8 2

in which to a format of
(person_id1, person_id2, person_id3, person_id4, household_id)

in which all person_ids with the same household_ids are group together in which the first 4 person_ids are in one row, along with the household_ids, those are left are left to put in another row)
e.g. after denormalization we have

1 2 3 4 1
4 5 6 null 1
7 8 2

thanks in advance!!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-21 : 18:07:42

Won't ask why.
Something like

insert tbl2
select household_id, min(Person_id),
(select top 1 Person_id from (select top 2 Person_id from tbl t2 where t1.household_id = t2.household_id order by Person_id desc) as a) order vt Person_id) ,
(select top 1 Person_id from (select top 3 Person_id from tbl t2 where t1.household_id = t2.household_id order by Person_id desc) as a) order vt Person_id) ,
(select top 1 Person_id from (select top 4 Person_id from tbl t2 where t1.household_id = t2.household_id order by Person_id desc) as a) order vt Person_id)
from tbl t2
group by household_id

then keep doing it for 5th,6th, ... entries. Can put inh loop with dynamic sql if you wish.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-22 : 16:51:21

I won't ask why as well (sounds like you should be using a report writer ...) but it still was kind of a fun challenge to do:

(and very doable)

declare @t table (person_id int, household_id int)
insert into @t
select 1,1 union
select 2,1 union
select 3,1 union
select 4,1 union
select 5,1 union
select 6,1 union
select 7,2 union
select 8,2

select
max(case when col = 0 then person_id else null end) as person_id1,
max(case when col = 1 then person_id else null end) as person_id2,
max(case when col = 2 then person_id else null end) as person_id3,
max(case when col = 3 then person_id else null end) as person_id4,
household_id
FROM
(
select household_id, person_id, (position-1) % 4 as Col, (position-1) / 4 as Row
from
(
select household_id, person_id,
(select count(*) from @t t2 where t2.household_id = t1.houseHold_id and t2.person_id <= t1.person_id)
as Position
From
@t t1
) a
)
b
group by household_id, Row
order by household_id, row

Please don't ask me to explain how it works .....but it does, for any number of households or persons ....

- Jeff

Edited by - jsmith8858 on 04/22/2003 16:51:47

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 21:00:42

An alternative combining RobVolk and Jsmiths8858 solutions in case you don't want to do it in real time because of performance reasons.

declare @t table (person_id int, household_id int, col_id int, row_id int)
insert into @t
select 1,1,null,null union
select 2,1,null,null union
select 3,1,null,null union
select 4,1,null,null union
select 5,1,null,null union
select 6,1,null,null union
select 7,2,null,null union
select 8,2,null,null

DECLARE @col1 varchar(1), @pos int
SELECT @pos=0
UPDATE @t
SET @pos = CASE @col1 WHEN household_id THEN @pos+1 ELSE 1 END,
col_id = (@pos -1) % 4,
row_id = (@pos-1) / 4,
@col1 = household_id

select
max(case when col_id = 0 then person_id else null end) as person_id1,
max(case when col_id = 1 then person_id else null end) as person_id2,
max(case when col_id = 2 then person_id else null end) as person_id3,
max(case when col_id = 3 then person_id else null end) as person_id4,
household_id
FROM
@t
group by household_id, row_id
order by household_id, row_id

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 21:45:21

Note that in ValterBorges post the update processes rows one by one and depends on them being in the order of household_id, person_id so there should be a clustered index on these fields otherwise you could get incorrect results.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 22:04:09

Thanks NR

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-23 : 10:32:59

OK, I gotta ask WHY?

Is there anything that's prevent n number of household members? You object (View or Table) would have to be rebuilt after the inspection of MAX(personId).

And, if you only wnated the first 4 PersonIds, what does that mean. How do you distinguish (arbitraraly?) who makes the list.

I'm sure you want to take in account the breadwinner.

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 10:42:40

Brett - there can be an unlimited number of persons per household. It wraps to as many different rows as needed. Run the example and check it out yourself.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources