hello
TableT1 has
T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2
How can I write my view to report these in
MyID, MyDate1, MyNote1, MyCharge1
MyID, MyDate2,MyNote2, MyCharge2
format?
You could use the following SQL Statement to make this happen:
select myid,mydate1,mynote1,mychange1
from MyInfo
union
select myid,mydate2,mynote2,mychange2
from MyInfo
order by myid
It would return appear something like the following:
MyID MyDate MyNote MyChange
1 2006-08-07 00:00:00.000 Called Dealer For Customer Service Changed his mail cost for 15 to 20
1 2006-08-08 00:00:00.000 Faxed Information to Dealer Changed Status to Enrolled
2 2006-08-04 00:00:00.000 Enrolling of Dealer Enrolled Dealer in Oil Change Mail Piece
2 2006-08-09 00:00:00.000 Dealer Called Changed to Tune-Up Mail Piece
Let me know if this works for you.
crusso
|||You should actually normalize your table so that it is easier to work with. You can do one of the following with your existing table structure:
select t.MyID
, case r.n when 1 then MyDate1 when 2 then MyDate2 end as MyDate
, case r.n when 1 then MyNote1 when 2 then MyNote2 end as MyNote
, case r.n when 1 then MyCharge1 when 2 then MyCharge2 end as MyCharge
from T1 as t
cross join (select 1 union all select 2) as r(n)
-- or
select t.MyID, t.MyDate1 as MyDate, t.MyNote1 as MyNote, t.MyCharge1 as MyCharge
from T1 as t
union all
select t.MyID, t.MyDate2 as MyDate, t.MyNote2 as MyNote, t.MyCharge2 as MyCharge
from T1 as t
