see answer below
> -----Original Message-----
> From: Erma Fernando [mailto:[EMAIL PROTECTED]]
>
> I have a table A with the following data (Table A has 3
> columns id, name,
> value)
>
> id name value
> 10 P1 20
> 10 P2 60
> 10 P3 12.5
> 10 P4 26
> 20 P1 100
> 20 P2 90
> 20 P3 15
> 20 P4 36
> 30 P1 60
> 30 P2 50
> 30 P3 11.5
> 30 P4 13
> .... more rows
>
> I want to insert this data into table B as follows (Table B
> has 4 columns
> id, P1, P2, P3, P4)
>
>
> id P1 P2 P3 P4
> 10 20 60 12.5 26
> 20 100 90 15 36
> 30 60 50 11.5 13
> ... more rows
>
> Can you suggest a Sql statement for the insert into table B.
insert into B (id, p1, p2, p3, p4)
select
a1.id, a1.value, a2.value, a3.value, a4.value
from
A a1, A a2, A a3, A a4
where
a1.name = 'P1'
and a1.id = a2.id
and a2.name = 'P2'
and a1.id = a3.id
and a3.name = 'P3'
and a1.id = a4.id
and a4.name = 'P4' ;
Of course, this assumes that there is always a value for all four 'names' in table A.
------
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
