row to column

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