March 11, 2010 at 10:52 am
I am working on a system that tracks office moves within my company. I have a Moves table that contains scheduled moves. Each row in the Moves table has a unique ID. Each move has a number of possible "details" that accompany it, e.g., Paint Location, Move Furniture, etc. To handle this, I created a MoveDetail table that looks like this:
ID MoveDetail
1Paint Location
2Clean Location
3Supply Setup
4Remove Previous Correspondence
5Check Furniture
6Move Furniture
I also created a Move2Detail lookup table that joins the Moves table to the MoveDetail table. The Move2Detail table only has 2 columns, MID which refers to the ID field in the Moves table, and MDID which refers to the ID field in the MoveDetail table. I did this instead of just adding 6 columns to the Moves table called PaintLoc, CleanLoc, etc. because I think it's best practices to model data as above. Currently the Move2Detail table has 2 rows:
MID MDID
1 2
1 4
I think I've shot myself in the foot, though, because what I need to get out of the database is something like this:
ID D1 D2 D3 D4 D5 D6
1 0 1 0 1 0 0
where the columns D1-D6 represent the 6 possible Move Details: 1 if it's present, 0 if it isn't.
I've come up with something, but it involves 3 temp tables and is pretty ugly. Here's my current code:
create table #temp(mid int, mdid int, movedetail varchar(50))
create table #temp2(mid int, detail1 int, detail2 int, detail3 int, detail4 int, detail5 int, detail6 int)
create table #temp3(mid int, mdid1 int, mdid2 int, mdid3 int, mdid4 int, mdid5 int, mdid6 int)
insert into #temp
select m.id as mid, md.id as mdid, md.movedetail from moves m
left outer join move2detail m2d
on m.[id] = m2d.mid
left outer join movedetail md
on m2d.mdid = md.[id]
insert into #temp2
select mid,
max(case when rk = 1 then mdid end) as detail1,
max(case when rk = 2 then mdid end) as detail2,
max(case when rk = 3 then mdid end) as detail3,
max(case when rk = 4 then mdid end) as detail4,
max(case when rk = 5 then mdid end) as detail5,
max(case when rk = 6 then mdid end) as detail6
from (select mid, mdid, movedetail,
(select count(*)
from #temp as b
where b.mid = a.mid
and b.mdid < = a.mdid) as rk
from #temp as a) as a
where mid = 1
group by mid
insert into #temp3
select mid,
case when detail1 = 1 then 1
when detail2 = 1 then 1
when detail3 = 1 then 1
when detail4 = 1 then 1
when detail5 = 1 then 1
when detail6 = 1 then 1
else 0
end,
case when detail1 = 2 then 1
when detail2 = 2 then 1
when detail3 = 2 then 1
when detail4 = 2 then 1
when detail5 = 2 then 1
when detail6 = 2 then 1
else 0
end,case when detail1 = 3 then 1
when detail2 = 3 then 1
when detail3 = 3 then 1
when detail4 = 3 then 1
when detail5 = 3 then 1
when detail6 = 3 then 1
else 0
end,case when detail1 = 4 then 1
when detail2 = 4 then 1
when detail3 = 4 then 1
when detail4 = 4 then 1
when detail5 = 4 then 1
when detail6 = 4 then 1
else 0
end,case when detail1 = 5 then 1
when detail2 = 5 then 1
when detail3 = 5 then 1
when detail4 = 5 then 1
when detail5 = 5 then 1
when detail6 = 5 then 1
else 0
end,case when detail1 = 6 then 1
when detail2 = 6 then 1
when detail3 = 6 then 1
when detail4 = 6 then 1
when detail5 = 6 then 1
when detail6 = 6 then 1
else 0
end
from #temp2
select * from #temp3
drop table #temp
drop table #temp2
drop table #temp3
There has to be a better way to do this! NB: I'm working in SQL 2000, so I don't have access to PIVOT or ROWNUMBER() or any of that great stuff. I'm hoping the answer isn't to just put 6 columns in my Moves table....
Thanks!
March 11, 2010 at 9:17 pm
Is this what are you looking for?
CREATE TABLE #MoveDetail (
ID int,
MoveDetail varchar(50)
)
INSERT INTO #MoveDetail
(ID, MoveDetail)
SELECT 1, 'Paint Location'
UNION
SELECT 2, 'Clean Location'
UNION
SELECT 3, 'Supply Setup'
UNION
SELECT 4, 'Remove Previous Correspondence'
UNION
SELECT 5, 'Check Furniture'
UNION
SELECT 6, 'Move Furniture'
CREATE TABLE #Move2Detail (
MID int,
MDID int
)
INSERT INTO #Move2Detail( MID, MDID )
SELECT 1, 2
UNION
SELECT 1,4
select m2d.mid as mid,
max(case when md.id = 1 then 1 else 0 end) as detail1,
max(case when md.id = 2 then 1 else 0 end) as detail2,
max(case when md.id = 3 then 1 else 0 end) as detail3,
max(case when md.id = 4 then 1 else 0 end) as detail4,
max(case when md.id = 5 then 1 else 0 end) as detail5,
max(case when md.id = 6 then 1 else 0 end) as detail6
from #move2detail m2d
left outer join #movedetail md
on m2d.mdid = md.[id]
GROUP BY m2d.mid
_____________
Code for TallyGenerator
March 12, 2010 at 8:44 am
Yes! Thank you SO much! I knew my way was just too too dumb, but I just couldn't figure out any other way of doing it. My mistake was taking the MoveDetail table as a starting point instead of the Move2Detail table (at least I think that was my mistake).
Again, thank you!:-D
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply