Re: SQL Statement

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 1998/02/17
Message-ID: <6cc0jg$7v4$1@hermes.is.co.za>#1/1

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 Received on Tue Feb 17 1998 - 00:00:00 CST