Home » SQL & PL/SQL » SQL & PL/SQL » How to get output like this
icon5.gif  How to get output like this [message #232698] Mon, 23 April 2007 01:17 Go to next message
naimish_hit
Messages: 34
Registered: March 2007
Member
Hi,

I have Master / Detail table like this:

Cost_Master
-----------
Cost_Head_ID (PK)
Cost_Head

Cost_Detail
-----------
ID (PK) (PK)
Cost_Head_ID
Cost_Item

I want to display data like this:

-----------------------------------
Cost Head 1
...Cost Item 1
...Cost Item 2
Cost Head 2
...Cost Item 3
...Cost Item 4

-----------------------------------

Is this possible to do in single Select statement ? If yes then How ?

Regards,

Naimish Dave

[Updated on: Mon, 23 April 2007 01:18]

Report message to a moderator

Re: Is this possible? [message #232713 is a reply to message #232698] Mon, 23 April 2007 01:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Please edit your post so that you make use of CODE tags. Your required output is not very clear this way.

MHE
Re: Is this possible? [message #232723 is a reply to message #232698] Mon, 23 April 2007 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is possible.
Put create table and insert statements to build an exemple and will show you how.

Regards
Michel
Re: Is this possible? [message #232748 is a reply to message #232698] Mon, 23 April 2007 03:04 Go to previous messageGo to next message
naimish_hit
Messages: 34
Registered: March 2007
Member
Hi,

Here are create and insert statements:

Cost_Head_Master

CREATE TABLE  "COST_HEAD_MASTER" 
   (	"COST_HEAD_ID" NUMBER NOT NULL ENABLE, 
	"COST_HEAD" VARCHAR2(150) NOT NULL ENABLE, 
	"ISACTIVE" CHAR(1), 
	 CONSTRAINT "COST_HEAD_MASTER_PK" PRIMARY KEY ("COST_HEAD_ID") ENABLE
   )
/



Cost_Item_Master

CREATE TABLE  "COST_ITEM_MASTER" 
   (	"COST_ITEM_ID" NUMBER, 
	"COST_HEAD_ID" NUMBER NOT NULL ENABLE, 
	"COST_ITEM" VARCHAR2(150) NOT NULL ENABLE, 
	"ISACTIVE" CHAR(1) NOT NULL ENABLE, 
	 CONSTRAINT "COST_ITEM_MASTER_PK" PRIMARY KEY ("COST_ITEM_ID") ENABLE, 
	 CONSTRAINT "COST_ITEM_MASTER_FK" FOREIGN KEY ("COST_HEAD_ID")
	  REFERENCES  "COST_HEAD_MASTER" ("COST_HEAD_ID") ON DELETE CASCADE ENABLE
   )
/


Insert Statements

insert into Cost_Head_Master values(1, 'Cost Head 1', 'Y')
insert into Cost_Head_Master values(2, 'Cost Head 2', 'Y')

insert into Cost_Item_Master(1, 1, 'Cost Item 1', 'Y')
insert into Cost_Item_Master(2, 1, 'Cost Item 2', 'Y')
insert into Cost_Item_Master(2, 2, 'Cost Item 3', 'Y')
insert into Cost_Item_Master(2, 2, 'Cost Item 4', 'Y')


Please do the needful.

Regards,

Naimish Dave
Re: Is this possible? [message #232750 is a reply to message #232748] Mon, 23 April 2007 03:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The script:
CREATE TABLE  cost_head_master
   (    cost_head_id NUMBER NOT NULL ENABLE,
    cost_head VARCHAR2(20) NOT NULL ENABLE,
    isactive CHAR(1),
     CONSTRAINT cost_head_master_pk PRIMARY KEY (cost_head_id) ENABLE
   )
/
CREATE TABLE  cost_item_master
   (    cost_item_id NUMBER,
    cost_head_id NUMBER NOT NULL ENABLE,
    cost_item VARCHAR2(20) NOT NULL ENABLE,
    isactive CHAR(1) NOT NULL ENABLE,
     CONSTRAINT cost_item_master_pk PRIMARY KEY (cost_item_id) ENABLE,
     CONSTRAINT cost_item_master_fk FOREIGN KEY (cost_head_id)
      REFERENCES  cost_head_master (cost_head_id) ON DELETE CASCADE ENABLE
   )
/
INSERT INTO cost_head_master
VALUES      (1
           , 'Cost Head 1'
           , 'Y'
            )
/
            
INSERT INTO cost_head_master
VALUES      (2
           , 'Cost Head 2'
           , 'Y'
            )
/

INSERT INTO cost_item_master
VALUES      (1
           , 1
           , 'Cost Item 1'
           , 'Y'
            )
/

INSERT INTO cost_item_master
VALUES      (2
           , 1
           , 'Cost Item 2'
           , 'Y'
            )
/

INSERT INTO cost_item_master
VALUES      (3
           , 2
           , 'Cost Item 3'
           , 'Y'
            )
/

INSERT INTO cost_item_master
VALUES      (4
           , 2
           , 'Cost Item 4'
           , 'Y'
            )
/

SELECT cost_text
FROM ( SELECT h.cost_head cost_text
            , h.cost_head_id
            , 1 rn
       FROM   cost_head_master h
       UNION ALL 
       SELECT '...'||i.cost_item cost_text
            , h.cost_head_id
            , 2 rn
       FROM   cost_head_master h
          ,   cost_item_master i 
       WHERE  h.cost_head_id = i.cost_head_id
    )
ORDER BY cost_head_id, rn
/

DROP TABLE cost_item_master
/
DROP TABLe cost_head_master
/


The run:
SQL> @orafaq

Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


COST_TEXT
-----------------------
Cost Head 1
...Cost Item 1
...Cost Item 2
Cost Head 2
...Cost Item 3
...Cost Item 4

6 rows selected.


Table dropped.


Table dropped.

SQL>


MHE

EDIT: thank you for providing the scripts (although they did contain some flaws).

[Updated on: Mon, 23 April 2007 05:00]

Report message to a moderator

Re: Is this possible? [message #232796 is a reply to message #232748] Mon, 23 April 2007 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT decode(grouping(cost_item),0,'...'||cost_item,cost_head) cost_text
  2  FROM ( SELECT h.cost_head, i.cost_item
  3         FROM cost_head_master h, cost_item_master i 
  4         WHERE h.cost_head_id = i.cost_head_id
  5       )
  6  GROUP BY rollup(cost_head, cost_item)
  7  HAVING grouping(cost_head) = 0
  8  ORDER BY cost_head, decode(grouping(cost_item),1,null,cost_item) nulls first
  9  /
COST_TEXT
-----------------------
Cost Head 1
...Cost Item 1
...Cost Item 2
Cost Head 2
...Cost Item 3
...Cost Item 4

6 rows selected.

Thanks both for the script.

Regards
Michel
Re: Is this possible? [message #232876 is a reply to message #232796] Mon, 23 April 2007 14:33 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Out of interest...
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> select h.cost_head cost_text , h.cost_head_id,
  2         cursor (SELECT i.cost_item
  3                         FROM  cost_item_master i
  4                         WHERE  h.cost_head_id = i.cost_head_id)
  5  FROM   cost_head_master h
  6  /

COST_TEXT            COST_HEAD_ID CURSOR(SELECTI.COST_
-------------------- ------------ --------------------
Cost Head 1                     1 CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

COST_ITEM
--------------------
Cost Item 1
Cost Item 2

Cost Head 2                     2 CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

COST_ITEM
--------------------
Cost Item 3
Cost Item 4


SQL> 
Re: Is this possible? [message #232906 is a reply to message #232876] Mon, 23 April 2007 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Andrew,

Not really out of interest.
It's good to have a recall of a syntax that can be useful (out of SQL*Plus Smile ).

Regards
Michel
icon5.gif  How to get output like this ? [message #235797 is a reply to message #232698] Tue, 08 May 2007 01:04 Go to previous messageGo to next message
naimish_hit
Messages: 34
Registered: March 2007
Member
Hi,

I have 3 tables, cost_head_master, cost_item_master and pda_template.

I want output like this,

Cost Head 1
..Cost Item 1
..Cost Item 2
..Cost Item 3
Cost Head 2
..Cost Item 4
..Cost Item 5
..Cost Item 6
.
.

Structure of these tables are given below.

Regards,

Naimish R. Dave
Re: How to get output like this ? [message #235800 is a reply to message #235797] Tue, 08 May 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the same topic as http://www.orafaq.com/forum/m/232698/102589/#msg_232698
This one is locked.

Regards
Michel
What would be the query for this ? [message #235808 is a reply to message #232698] Tue, 08 May 2007 01:24 Go to previous messageGo to next message
naimish_hit
Messages: 34
Registered: March 2007
Member
Hi,

Quote:
Senior Members are requested not to lock this topic as its not similiar to any other posting


I have 3 tables, cost_head_master, cost_item_master and pda_template.

I want to query pda_template and want output like this,

Cost Head 1
..Cost Item 1
..Cost Item 2
..Cost Item 3
Cost Head 2
..Cost Item 4
..Cost Item 5
Cost Head 3
..Cost Item 6
.
.

What would be the query for this ??

Structure of these tables are given below.

CREATE TABLE cost_head_master (
  cost_head_id NUMBER        NOT NULL,
  cost_head    VARCHAR2(150) NOT NULL,
  isactive     CHAR(1)       NULL
)
/



CREATE TABLE cost_item_master (
  cost_item_id NUMBER        NOT NULL,
  cost_item    VARCHAR2(150) NOT NULL,
  isactive     CHAR(1)       NOT NULL
)
/



CREATE TABLE pda_template (
  pda_templateid NUMBER NOT NULL,
  operatorid     NUMBER NOT NULL,
  cost_head_id   NUMBER NOT NULL,
  cost_item_id   NUMBER NOT NULL
)
/

ALTER TABLE cost_head_master
  ADD CONSTRAINT cost_head_master_pk PRIMARY KEY (
    cost_head_id
  )
/


ALTER TABLE cost_item_master
  ADD CONSTRAINT cost_item_master_pk PRIMARY KEY (
    cost_item_id
  )
/


ALTER TABLE pda_template
  ADD CONSTRAINT pda_template_pk PRIMARY KEY (
    pda_templateid
  )
/

ALTER TABLE pda_template
  ADD CONSTRAINT pda_template_fk2 FOREIGN KEY (
    cost_head_id
  ) REFERENCES cost_head_master (
    cost_head_id
  ) ON DELETE CASCADE
/

ALTER TABLE pda_template
  ADD CONSTRAINT pda_template_fk3 FOREIGN KEY (
    cost_item_id
  ) REFERENCES cost_item_master (
    cost_item_id
  ) ON DELETE CASCADE
/



insert into cost_head_master values(1, 'Cost Head 1', 'Y');
insert into cost_head_master values(2, 'Cost Head 2', 'Y');
insert into cost_head_master values(3, 'Cost Head 3', 'Y');

insert into cost_item_master values(1, 'Cost Item 1', 'Y');
insert into cost_item_master values(2, 'Cost Item 2', 'Y');
insert into cost_item_master values(3, 'Cost Item 3', 'Y');
insert into cost_item_master values(4, 'Cost Item 4', 'Y');
insert into cost_item_master values(5, 'Cost Item 5', 'Y');
insert into cost_item_master values(6, 'Cost Item 6', 'Y');

insert into pda_template values(1, 0, 1, 1);
insert into pda_template values(2, 0, 1, 2);
insert into pda_template values(3, 0, 2, 3);
insert into pda_template values(4, 0, 2, 4);
insert into pda_template values(5, 0, 3, 5);
insert into pda_template values(6, 0, 3, 6);


Regards,

Naimish R. Dave

[Updated on: Tue, 08 May 2007 01:25]

Report message to a moderator

Re: What would be the query for this ? [message #235810 is a reply to message #235808] Tue, 08 May 2007 01:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
First of all: thank you very much for providing the necessary scripts. But couldn't you work this one out starting from your previous thread? It seems to be just a small elaboration.

naimish_hit wrote on Tue, 08 May 2007 08:24
Hi,

Quote:
Senior Members are requested not to lock this topic as its not similiar to any other posting



For your info. That's not your call, nor is it the decision of senior members. It is a moderator's decision. Moderators try to keep things orderly here.

Anyway, we'll be back with some suggestions Wink

MHE

[Updated on: Tue, 08 May 2007 01:35]

Report message to a moderator

Re: What would be the query for this ? [message #235814 is a reply to message #235808] Tue, 08 May 2007 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From what we posted in your already thread what did you already try to solve this one?

Regards
Michel
Re: What would be the query for this ? [message #235819 is a reply to message #235808] Tue, 08 May 2007 01:44 Go to previous messageGo to next message
naimish_hit
Messages: 34
Registered: March 2007
Member
Thanks Michel,

Earlier there was a foreign key set in cost_item_master for cost_head_master but now these both tables are independent without any relation.

Now I am relating both the tables (i.e. cost_head_master and cost_item_master) in pda_template.

And this where I am not getting how to write query for this Sad ?

Regards,

Naimish Dave
Re: What would be the query for this ? [message #235820 is a reply to message #235819] Tue, 08 May 2007 01:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It is a simple join if I'm not mistaken. Here's my attempt.

The script:
col cost_text format a40
set pages 120

CREATE TABLE cost_head_master (
  cost_head_id NUMBER        NOT NULL,
  cost_head    VARCHAR2(150) NOT NULL,
  isactive     CHAR(1)       NULL
)
/



CREATE TABLE cost_item_master (
  cost_item_id NUMBER        NOT NULL,
  cost_item    VARCHAR2(150) NOT NULL,
  isactive     CHAR(1)       NOT NULL
)
/



CREATE TABLE pda_template (
  pda_templateid NUMBER NOT NULL,
  operatorid     NUMBER NOT NULL,
  cost_head_id   NUMBER NOT NULL,
  cost_item_id   NUMBER NOT NULL
)
/

ALTER TABLE cost_head_master
  ADD CONSTRAINT cost_head_master_pk PRIMARY KEY (
    cost_head_id
  )
/


ALTER TABLE cost_item_master
  ADD CONSTRAINT cost_item_master_pk PRIMARY KEY (
    cost_item_id
  )
/


ALTER TABLE pda_template
  ADD CONSTRAINT pda_template_pk PRIMARY KEY (
    pda_templateid
  )
/

ALTER TABLE pda_template
  ADD CONSTRAINT pda_template_fk2 FOREIGN KEY (
    cost_head_id
  ) REFERENCES cost_head_master (
    cost_head_id
  ) ON DELETE CASCADE
/

ALTER TABLE pda_template
  ADD CONSTRAINT pda_template_fk3 FOREIGN KEY (
    cost_item_id
  ) REFERENCES cost_item_master (
    cost_item_id
  ) ON DELETE CASCADE
/

insert into cost_head_master values(1, 'Cost Head 1', 'Y');
insert into cost_head_master values(2, 'Cost Head 2', 'Y');
insert into cost_head_master values(3, 'Cost Head 3', 'Y');

insert into cost_item_master values(1, 'Cost Item 1', 'Y');
insert into cost_item_master values(2, 'Cost Item 2', 'Y');
insert into cost_item_master values(3, 'Cost Item 3', 'Y');
insert into cost_item_master values(4, 'Cost Item 4', 'Y');
insert into cost_item_master values(5, 'Cost Item 5', 'Y');
insert into cost_item_master values(6, 'Cost Item 6', 'Y');

insert into pda_template values(1, 0, 1, 1);
insert into pda_template values(2, 0, 1, 2);
insert into pda_template values(3, 0, 2, 3);
insert into pda_template values(4, 0, 2, 4);
insert into pda_template values(5, 0, 3, 5);
insert into pda_template values(6, 0, 3, 6);


SELECT v.cost_text
FROM ( SELECT distinct
              h.cost_head cost_text
            , h.cost_head_id
            , 1 rn
       FROM   cost_head_master h
          ,   pda_template     t
       WHERE  t.cost_head_id = h.cost_head_id
       UNION ALL 
       SELECT '...'||i.cost_item cost_text
            , t.cost_head_id
            , 2 rn
       FROM   cost_item_master i 
          ,   pda_template     t
       WHERE  t.cost_item_id = i.cost_item_id
    ) v
ORDER BY cost_head_id, rn, cost_text
/

DROP TABLE pda_template
/
DROP TABLE cost_item_master
/
DROP TABLE cost_head_master
/

The run:
SQL> @orafaq

Table created.

...<snip>...

COST_TEXT
----------------------------------------
Cost Head 1
...Cost Item 1
...Cost Item 2
Cost Head 2
...Cost Item 3
...Cost Item 4
Cost Head 3
...Cost Item 5
...Cost Item 6

9 rows selected.


Table dropped.


Table dropped.


Table dropped.


MHE
Re: What would be the query for this ? [message #235835 is a reply to message #235808] Tue, 08 May 2007 02:16 Go to previous message
naimish_hit
Messages: 34
Registered: March 2007
Member
Thanks a lot Maaher.. It worked

Regards,

Naimish R. Dave
Previous Topic: order by day
Next Topic: Update query is not working ?
Goto Forum:
  


Current Time: Thu Dec 08 10:05:53 CST 2016

Total time taken to generate the page: 0.13092 seconds