RE: Inserting data from long table into wide table

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 22 Mar 2001 12:01:31 -0800
Message-ID: <F001.002D52E4.20010322115138@fatcity.com>

see answer below

> -----Original Message-----
> From: Erma Fernando [mailto:cecfernan_at_hotmail.com]
>
> 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 Received on Thu Mar 22 2001 - 14:01:31 CST