sql inserting question

[lendbz]

Hello,

I have a question:

I have a Table with information:

create table info1 (
ID,
name,
address,
....
);

Now I want to import those data to another table, except in this format:

create table info2 (
ID,
label,
data
);

Say if i have data in info1 table like this:

ID name Address
------------------------
1 tom 1 2nd st
2 toby 3 9th st
...

I want to import those data to table info2 will look like this:

ID| Label | data
------------------
1 name tom
1 address 1 2nd st
2 name toby
2 address 3 9th st
...


Now i could write multiple insert statement to do this, like

Insert into info2 (ID, label, info)
select ID, 'name', name
from info1
go
Insert into info2 (ID, label, info)
select ID, 'address', address
from info1
go

and so on and so on...

Is there a easier way to do this?

Any ways to achieve this in one sql statement??


Thanks in advance

lendbz

 

[gmmastros]

Insert into info2 (ID, label, info)
 select ID, 'name', name
   from info1
[!]Union All[/!]
  select ID, 'address', address
    from info1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lendbz]

aww thank you that's simplier!

Now for the reverse direction, from info2 to info1...

I normally do:

Insert into Info1 (Id, name, address,...)
select ID,
max(case when label = 'name' then info else NULL end),
max(case when label = 'address' then info else NULL end),
...
from info2
group by ID

Is this a practical way to do it? or there is another way?

THanks.
lendbz

 

[gmmastros]

There may be another way to do it, but the way you describe (for the reverse direction), is exactly the way I would do it in one of my apps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lendbz]

thanks