Re: SQL question

How about

Select Product, Lvl1, Lvl2, Lvl3, Lvl4 from

(Select Distinct Product from Products) Product_List,
(Select Product, Code Lvl1  from Products where Level = 1) Level1,
(Select Product, Code Lvl2 from Products where Level = 2) Level2,
(Select Product, Code Lvl3 from Products where Level = 3) Level3,
(Select Product, Code Lvl4 from Products where Level = 4) Level4

Product_List.Product = Level1.Product (+) and
Product_List.Product = Level2.Product (+) and
Product_List.Product = Level3.Product (+) and
Product_List.Product = Level4.Product (+);

Pulling the products separately into the Product_list view is unnecessary if you can guarantee that all 4 records exist for every product.

HTH, Jim Reinardy

<terry_stjean_at_my-deja.com> wrote in message news:8n9f2u$bpj$1_at_nnrp1.deja.com...
> I have a table which contain 4 records per product. The 4 records
> represent the products hierarchy codes. A sequence number identifies
> the hierarchy level.
> e.g. Product Level Code
> A 1 A2
> A 2 C4
> A 3 10
> A 4 01
> Is it possible in SQL to reformat this to the following:
> Product Lvl1 Lvl2 Lvl3 Lvl4
> A A2 C4 10 01
>
> Then I will join this onto our main sales file.
>
> Terry
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Aug 14 2000 - 21:37:54 CEST