Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: Assistance with the following sql

    Oracle 8.1.7 (yes, I know it's old )

    Am attempting do the following in sqlplus

    We have a products table that has a number of stock items relating to a number of customers. For reporting purposes, what I would like to do is to query all stock items relating to Customer code ‘0’, and show the price for that stock code for another (variable) customer for example customer 1002.
    Each stock code should be on its own row, with customer 0 price in the first column, and customer 1002 price in the second column.

    Stock codes for other customers should not be displayed. Customer 0 will always return a price. Customer 1002 may not alyways have a price.

    PRODUCTS TABLE

    STOCKCODE CUSTOMER PRICE
    00000001 0 $1000
    00000002 0 $500
    00000003 0 $750
    00000004 1002 $2000
    00000005 1000 $1700
    00000001 1002 $2000
    00000005 0 $1500
    00000005 1007 $1900
    00000001 1010 $7500
    00000004 0 $2500
    00000005 1002 $1950

    DESIRED OUTPUT

    STOCK CODE PRICE_CUST0 PRICE_CUST1002
    00000001 $1000 $2000
    00000002 $500
    00000003 $750
    00000004 $2500 $2000
    00000005 $1500 $1950


    The actual query and table structures are more complex, but if I get the logic on how to do the above, I should be able to figure out the rest.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Your requirement seems to be a classical pivot (rows to columns) query.

    You may find its explanation with example even for old Oracle versions (search for DECODE there) e.g. in this article: http://www.oracle-base.com/articles/...tors_11gR1.php

  3. #3
    Join Date
    Sep 2011
    Posts
    2

    Thanks Flyboy

    Your response confirmed that my line of thought was heading in the right direction. All working nicely now using decode and grouping.

    Many thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •