Transform Data into Report Format

[harry010]

Hi all,

Firstly- I've written a PL/SQl procedure to what I'm asking about in this posting.

However, I'd like to do it with a single(or nested) SQL selection statement (I don't think its possible, but I'm no expert and was interested to hear if anyone has a solution- or just a hint :).

Is it possible to format, say a 10 column table, in such a way that it is grouped by 3 columns, sorted by 3 and which has a summary row of 3 columns after each of the first groupings(supplier).

Here is an example of the format I'm looking for: grouped by c1, c2, c3 and sorted by c1, c2:

[B]          c1,           c2,      c3,      c4,     c5,     c6,    c7,   c8,   c9,  c10 [/B]
         supplier1,   store1,  date3, invoice1,   sku88, data, data, data, data, data
         supplier1,   store1,  date4, invoice2,   sku88, data, data, data, data, data
                                                         sum   sum   sum

         supplier2,   store1,  date2, invoice1,   sku88, data, data, data, data, data
         supplier2,   store3,  date3, invoice2,   sku88, data, data, data, data, data
         supplier2,   store5,  date3, invoice4,   sku99, data, data, data, data, data
         supplier2,   store5,  date3, invoice5,   sku99, data, data, data, data, data
                                                         sum   sum   sum 

         supplier3,   store1,  date1, invoice2,   sku99, data, data, data, data, data
                                                         sum   sum   sum

Thanks for any ideas! :)

[debasisdas]

What is the table structure and what is the code that you are working on ?

[alit2002]

Hi

I think you need to check out the SQL 'BREAK' command. See below:

BREAK ON
To produce grouping of items as in a control break report, use the BREAK ON command, in conjunction with the ORDER BY option in the SELECT command.

Break on Item skip 2

SQLPLUS will examine each row and track the value in Item. When the value for Item changes, SQLPLUS will skip two lines. Also the value for Item is printed on the first line of its section to eliminate duplicate printing of each of these items for every row in each section.

If you want a grand total for the report, use the command:

Break on Item skip 2 on report

If you just use BREAK ON report, then the second BREAK ON command will override the first one.

Good luck
Alistair