Home » SQL & PL/SQL » SQL & PL/SQL » Formatting output - a query
| Formatting output - a query [message #8186] |
Sat, 02 August 2003 01:57  |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
Consider i have a table with following colums
Person_Name varchar2(20)
Product varchar2(15)
Sales_value number
-------------------------------------
It contains sales value for different persons for different products.
A general query will produce the output in following manner.
Person Product Value
==================================
John Butter 210
Mill Cheese 50
John Cheese 60
Smith Ice 100
Now i want the output in following manner
Butter Cheese Ice
Smith 0 0 100
JOhn 210 60 0
Mill 0 50 0
===========================================
I had tried by using "decode" ..but i was getting
Butter Cheese Ice
Smith 100
JOhn 210
John 60
Mill 50
========================================
i.e. i want that it should show one row each person.
It is possible using sql query or report builder , if yes , pls help.
Regards,
Katyal
|
|
|
|
|
|
|
|
| Re: Formatting output - a query [message #8216 is a reply to message #8207] |
Mon, 04 August 2003 13:53  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Looking at what you posted previously, I suspect that the problem has to do with inconsistent case within your data. You are probably receiving one line for 'JOhn' with an upper case 'O' and one line for 'John' with a lower case 'o'. This can be solved by adding either INITCAP or UPPER or LOWER. Below I have used the initial data that you provided, then a query with decode alone, which produces multiple lines per person_name, then a query with sum and decode, which produces only one line per person_name. Then I have changed one 'John' to 'JOhn' as your sample output indicates your data must be, then shown that the same query with sum and decode now produces multiple lines, then shown that, if you add INITCAP, it solves the problem and produces what you want.
SQL> -- initial test table and data:
SQL> CREATE TABLE your_table
2 (person_name VARCHAR2 (20),
3 product VARCHAR2 (15),
4 sales_value NUMBER)
5 /
Table created.
SQL> INSERT INTO your_table VALUES ('John', 'Butter', 210)
2 /
1 row created.
SQL> INSERT INTO your_table VALUES ('Mill', 'Cheese', 50)
2 /
1 row created.
SQL> INSERT INTO your_table VALUES ('John', 'Cheese', 60)
2 /
1 row created.
SQL> INSERT INTO your_table VALUES ('Smith', 'Ice', 100)
2 /
1 row created.
SQL> SELECT * FROM your_table
2 /
PERSON_NAME PRODUCT SALES_VALUE
-------------------- --------------- -----------
John Butter 210
Mill Cheese 50
John Cheese 60
Smith Ice 100
SQL> --
SQL> --
SQL> -- query using decode with sum produces one line per
SQL> -- combination of person_name and product:
SQL> SELECT person_name,
2 DECODE (product, 'Butter', sales_value) AS "Butter",
3 DECODE (product, 'Cheese', sales_value) AS "Cheese",
4 DECODE (product, 'Ice', sales_value) AS "Ice"
5 FROM your_table
6 /
PERSON_NAME Butter Cheese Ice
-------------------- ---------- ---------- ----------
John 210
Mill 50
John 60
Smith 100
SQL> --
SQL> --
SQL> -- query using sum and decode produces one line per person_name:
SQL> SELECT person_name,
2 SUM (DECODE (product, 'Butter', sales_value)) AS "Butter",
3 SUM (DECODE (product, 'Cheese', sales_value)) AS "Cheese",
4 SUM (DECODE (product, 'Ice', sales_value)) AS "Ice"
5 FROM your_table
6 GROUP BY person_name
7 /
PERSON_NAME Butter Cheese Ice
-------------------- ---------- ---------- ----------
John 210 60
Mill 50
Smith 100
SQL> --
SQL> --
SQL> -- modification of data to create 'JOhn' in addition to 'John':
SQL> UPDATE your_table
2 SET person_name = 'JOhn'
3 WHERE person_name = 'John'
4 AND product = 'Butter'
5 /
1 row updated.
SQL> SELECT * FROM your_table
2 /
PERSON_NAME PRODUCT SALES_VALUE
-------------------- --------------- -----------
JOhn Butter 210
Mill Cheese 50
John Cheese 60
Smith Ice 100
SQL> --
SQL> --
SQL> -- same query now produces two lines, one for 'JOhn' and one for 'John':
SQL> SELECT person_name,
2 SUM (DECODE (product, 'Butter', sales_value)) AS "Butter",
3 SUM (DECODE (product, 'Cheese', sales_value)) AS "Cheese",
4 SUM (DECODE (product, 'Ice', sales_value)) AS "Ice"
5 FROM your_table
6 GROUP BY person_name
7 /
PERSON_NAME Butter Cheese Ice
-------------------- ---------- ---------- ----------
JOhn 210
John 60
Mill 50
Smith 100
SQL> --
SQL> --
<b>SQL> -- usage of INITCAP or UPPER or LOWER function solves the problem:
SQL> SELECT INITCAP (person_name),
2 SUM (DECODE (product, 'Butter', sales_value)) AS "Butter",
3 SUM (DECODE (product, 'Cheese', sales_value)) AS "Cheese",
4 SUM (DECODE (product, 'Ice', sales_value)) AS "Ice"
5 FROM your_table
6 GROUP BY INITCAP (person_name)
7 /
INITCAP(PERSON_NAME) Butter Cheese Ice
-------------------- ---------- ---------- ----------
John 210 60
Mill 50
Smith 100</b>
|
|
|
|
| Re: Formatting output - a query [message #8219 is a reply to message #8216] |
Tue, 05 August 2003 00:18 |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
Firstly..thanks a lot , the sum decode is now working.
I have further query..would like to ask you , if i am not troubling you.
According to me , we can go for decode in case where the No. of products are limited , like in this case there are only 3 products i.e. butter , cheese , ice.
So we can hardcore product name and use it.
But..what to do if the products are unlimited..say 10-15-20.....
Still is there any way....by any means i.e. either using a sql or pl/sql block(using cursors) or D2k reports.
(please ignore the letter case like JOhn,John for the time being)
Please help.
Rajeev Katyal
|
|
|
|
|
|
| Re: Formatting output - a query [message #8243 is a reply to message #8219] |
Tue, 05 August 2003 16:03 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
This second method uses pl/sql. It loops through an implicit cursor to create the query (v_sql), then opens a ref cursor based on the query. I have just printed the ref cursor from SQL*Plus, however you could elso fetch into variables and output using DBMS_OUTPUT.
SQL> CREATE OR REPLACE PACKAGE types
2 AS
3 TYPE weak_ref_cursor IS REF CURSOR;
4 END types;
5 /
Package created.
SQL> CREATE OR REPLACE PROCEDURE your_procedure
2 (p_ref IN OUT types.weak_ref_cursor)
3 AS
4 v_sql VARCHAR2 (4000);
5 BEGIN
6 v_sql := 'SELECT person_name';
7 FOR rec IN
8 (SELECT DISTINCT product FROM your_table)
9 LOOP
10 v_sql := v_sql
11 || ',SUM(DECODE(product,'''
12 || rec.product
13 || ''',sales_value)) AS "'
14 || rec.product
15 || '"';
16 END LOOP;
17 v_sql := v_sql || ' FROM your_table GROUP BY person_name';
18 OPEN p_ref FOR v_sql;
19 END your_procedure;
20 /
Procedure created.
SQL> VARIABLE g_ref REFCURSOR
SQL> EXECUTE your_procedure (:g_ref)
PL/SQL procedure successfully completed.
SQL> PRINT g_ref
PERSON_NAME Butter Cheese Ice
-------------------- ---------- ---------- ----------
John 420 120
Mill 100
Smith 200
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 22:56:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
