Re: SQL Statement

From: David Vanidour <dvanidou_at_psc-cfp.gc.ca>
Date: 1998/02/17
Message-ID: <34E9F8F2.10F9@psc-cfp.gc.ca>#1/1

Billy Verreynne wrote:
>
> xyzer_at_samsung.co.kr wrote in message <34E8B643.A01F3110_at_samsung.co.kr>...
> >I have an odd query requirement that I am struggling with approaches
> >to. I'm not even sure what to call this type of query? Here's hoping
> >someone can help... Lets say we have the following table...
> >
> <snipped>
>
> If I understand your question directly:
>
> table1 =(sequence#,restaurant_ID,item_id,item_cost)
>
> output required:
> restaurant 1, item 1 cost, item 2 cost, ..., item-n cost
> restaurant 2, item 1 cost, item 2 cost, ..., item-n cost
> restaurant n, item 1 cost, item 2 cost, ..., item-n cost
>
> The closest you can get to it is with "normal" SQL is doing a group by on
> restaurant, by then you will have the following:
>
> output using group by on restaurant:
> restaurant_id item_id cost
> ------------- ------- -----
> restaurant 1 item-1 100.00
> restaurant 1 item-2 300.00
> ...
> restaurant 1 item-n 250.00
> restaurant 2 item-1 99.00
> ..etc..
>
> In order to pivot all the rows per restaurant into multiple columns in a
> single row you need to create a pivot table. The number of rows must equal
> the number of items, e.g.
>
> pivot table=(item_id, item1_flag, item2_flag, ..., itemn_flag).
>
> Let's say there are 5 items. The pivot table will look as follows:
> item_id item1_flag item2_flag item3_flag item4_flag item5_flag
> ------ ---------- ---------- ---------- ---------- ----------
> item 1 1 0 0 0 0
> item 2 0 1 0 0 0
> item 3 0 0 1 0 0
> item 4 0 0 0 1 0
> item 5 0 0 0 0 1
>
> Ok, first create a temporary table using the GROUP BY clause:
>
> CREATE TABLE restaurant_grouping
> UNRECOVERABLE AS
> SELECT
> restaurant_id,
> item_id,
> SUM(item_cost) ITEM_COST
> FROM table1
>
> Next, you use the pivot table to summarise all item costs per restaurant
> into a single row with multiple item cost columns:
>
> SELECT
> t1.restaurant_id,
> SUM(t1.item_cost * t2.item1_flag) ITEM1_COST,
> SUM(t1.item_cost * t2.item2_flag) ITEM2_COST,
> SUM(t1.item_cost * t2.item3_flag) ITEM3_COST,
> SUM(t1.item_cost * t2.item4_flag) ITEM4_COST,
> SUM(t1.item_cost * t2.item5_flag) ITEM5_COST
> FROM restaurant_grouping t1, pivot_table t2
> WHERE t1.item_id = t2.item_id
> GROUP BY restaurant
>
> Because of the flag values in then pivot table, only item 1 costs will be
> summarised into the item1 cost column - other item amounts are multiplied
> with zero before added to the item1 cost column. Ditto for the other item
> cost columns.
>
> It should be possible to combine all this into a single SQL statement.
>
> Hope you understand this, as I'm not good at explaining how to pivot rows
> into columns. :-)
>
> regards,
> Billy

Ya Billy, that will work... but I like Michael Ringbo's solution better. It's also much simpler.

ie:

Select restaurant_id,

       sum(decode(item_id,1,item_cost,0)),
       sum(decode(item_id,2,item_cost,0))

  from your_table
 group by restaurant_id;

Cheers Received on Tue Feb 17 1998 - 00:00:00 CST