Re: Getting two fields from the one table.

Hello mrstock,

I am curious as to how you would accomplish the pivot using just joins. E.g.:

given:

X Y
1 a
1 b
2 y
3 z
3 z
2 x

a trivial solution using decode, courtesy of Mr. Kyte, would be:

select x, max(decode(rn, 1, y, null)) val1, max(decode(rn, 2, y, null)) val2 from
 (select x, y, row_number() over (partition by x order by y) rn from t1) group by x

X VAL1 VAL2
1 a b
2 x y
3 z z

How would you do it with joins ? Genuinely curious ...

Rgds.

"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message news:Osidnc9TE-vt8iaiRVn-hg_at_comcast.com...
> you will need to join to each "price schema" structure separately
>
> your where clause is including two PS.CODES in the same record set, you
need
> to retrieve the two codes, and related price data, by joining to the
REFCODE
> table twice, once for each CODE, then joining from each of these to
related
> tables
>
> the DECODE, CASE and pivot suggestions are related but more appropriate
for
> when working with aggregate data in a matrix output.
>
> -- mcs
>
>
> "Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote in message
> news:T7zub.58$rG.1893_at_nnrp1.ozemail.com.au...
> | I use Golden to send SQLs to our Oracle database. Today's objective is
to
> | retrieve product data from the database. The product can have many EX
GST
> or
> | INC GST prices, and the prices are per colour. The following SQL will
give
> | me the EX GST per colour and the INC GST per colour. So if an EX & INC
> price
> | is available for a particular colour, I get two records. What i'd like
to
> | get is one record showing BOTH the EX & INC prices for that colour.
> |
> | SQL:
> |
> | /* Place the Division code here */
> | var DivCode string
> | var DivCode = "CHARACTER"
> |
> | select ps.code "Price Schema", s.stylecode "Style", s.name
"Description",
> | clr.clrcode "Col Code",
> | clr.clrname "Col Desc", sku.sizeval "Sizes",sz.sizeseq, kapn.apnnum
"APN",
> | p2.price "Price", seas.code "Season"
> | from sprice_style p1, sprice_clrsku p2, style s, clr, sku, kapn, sizes
sz,
> | refcode ps, refcode seas, refcode divn
> | where p1.styleidx=s.styleidx
> | and p1.spidx=p2.spidx
> | and p2.clridx=clr.clridx
> | and clr.clridx=sku.clridx
> | and sku.skuidx=kapn.skuidx(+)
> | and sku.sizeidx=sz.sizeidx
> | and ps.rcidx=p1.rcidx
> | and seas.rcidx(+)=clr.ref1
> | and divn.rcidx=s.ref1
> | and sku.active=1
> | and sku.sizeval is not null
> | and (ps.code='RRP EX GST' or ps.code='WHL EX GST')
> | and divn.code= :DivCode
> | order by s.stylecode;
> |
> | Output of THIS SQL:
> | WHL EX GST | 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY | L |
50
> |
> | 9317326271078 | 17.3 | WI97
> | RRP EX GST | 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY | L |
50
> |
> | 9317326271078 | 29.95 | WI97
> |
> | The SQL-output Goal:
> | 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY | L | 50 |
> 9317326271078
> | | 17.3 | 29.95 | WI97
> |
> | Having both prices in the same record. How can I change my SQL to do
this?
> |
> | ---------------------------------
> | If you need additional info, let me know.
> |
> |
> | --
> |
> | Julian Milano
> |
> |
> |
>
>
Received on Wed Nov 19 2003 - 18:31:16 CST