Re: Getting two fields from the one table.

i did not suggest doing a pivot with just joins

my point was that the original problem can be solved very directly with additional joins

"VC" <boston103_at_hotmail.com> wrote in message news:slUub.191787$275.672134_at_attbi_s53...
| I apologize for possible double-posting -- my provider is acting up.
|
| 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 - 21:21:51 CST