Convert single record to table

Hi all!

I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?

Thanks.

Try:

create table dbo.t3 (

pk_col int not null,

grp int not null,

Accnt int,

Assesed int,

Paid int,

constraint pk_t3 primary key (pk_col, grp) clustered

)

insert into dbo.t3(pk_col, grp, Accnt, Assesed, Paid)

select pk_col, 1 as grp, Accnt1as Accnt, Assesed1as Assesed, Paid1 as Paid

from dbo.t1

union all

select pk_col, 2 as grp, Accnt2, Assesed2, Paid2

from dbo.t1

union all

select pk_col, 3 as grp, Accnt3, Assesed3, Paid3

from dbo.t1

order by pk_col, grp

AMB

|||

Thanks! it worked great!