Home » SQL & PL/SQL » SQL & PL/SQL » Return result of query into a 6 columns table
Return result of query into a 6 columns table [message #425319] Thu, 08 October 2009 10:53 Go to next message
roseline43
Messages: 10
Registered: October 2009
Junior Member
Hi,

(It's my 1st post so sorry if the formatting is not right)
I would like to know How to return the result of my query in a table.
My Query has more than 500 results.
Here are my data:


CREATE TABLE  "REGELEMENTS" 
   (	"ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ID_DISQUE" NUMBER(10,0), 
	"NOM" VARCHAR2(100 CHAR), 
	"DESCRIPTIF" CLOB NOT NULL ENABLE, 
	"ID_DOSSIER" NUMBER(10,0), 
	"DBLVALUE" FLOAT(126) DEFAULT '0' NOT NULL ENABLE, 
	"NBSOUSELEMENTS" NUMBER(10,0) DEFAULT '0' NOT NULL ENABLE, 
	"CONTRAT" VARCHAR2(300 CHAR) DEFAULT '---' NOT NULL ENABLE, 
	"URL_CONTRAT" VARCHAR2(300 CHAR) DEFAULT '---' NOT NULL ENABLE, 
	"ACTION" VARCHAR2(300 CHAR) DEFAULT '---' NOT NULL ENABLE, 
	"DATE_" VARCHAR2(300 CHAR) DEFAULT '---' NOT NULL ENABLE, 
	"TYPE" VARCHAR2(45 CHAR) DEFAULT '---' NOT NULL ENABLE, 
	 CONSTRAINT "PRIMARY_25" PRIMARY KEY ("ID") ENABLE
   )



insert into regelements values(53976,10106,22j11201.tif, ,,0,0,---,---,---,---,---)
insert into regelements values(53977,10106,22j11202.tif, ,,0,0,---,---,---,---,---)
insert into regelements values(53978,10106,22j12101.tif, ,,0,0,---,---,---,---,---)
insert into regelements values(53979,10106,22j12102.tif, ,,0,0,---,---,---,---,---)
insert into regelements values(53980,10106,22j12201.tif, ,,0,0,---,---,---,---,---)
insert into regelements values(53981,10106,22j12202.tif, ,,0,0,---,---,---,---,---)
insert into regelements values(53982,10106,22j13101.tif, ,,0,0,---,---,---,---,---)
insert into regelements values(53983,10106,22j13102.tif, ,,0,0,---,---,---,---,---)


(I have 501 elements for the ID_DISQUE 10106 so I'm just gona past some of it. Tell me if you need me the post all 501)

select	 "REGELEMENTS"."ID" as "ID",
	 "REGELEMENTS"."ID_DISQUE" as "ID_DISK",
	 "REGELEMENTS"."NOM" as "NOM" 
 from	 "REGELEMENTS" "REGELEMENTS" 
 where 	 "REGELEMENTS"."ID_DISQUE" ='10106'


 Results
 
 NOM
22h01101.tif
22h02101.tif
22h02102.tif
22h02201.tif
22h02202.tif
22h03101.tif
22h03102.tif
22h03201.tif
22h03202.tif
22h04101.tif


Question: How can I have the result into a table?
I used the stragg2 function ( to use CLOB)
asktom.oracle.com/pls/asktom/f?p=100:11:237811788166177::::P11_QUESTION_ID:2196162600402

My result is too long in my row:

select  stragg2(REGELEMENTS.NOM), regelements.id_disque
  from  REGELEMENTS
  where REGELEMENTS.ID_DISQUE = 10106
  group by ID_Disque


ID_DISQUE
22h01101.tif,22h02101.tif,22h02102.tif,22h02202.tif,22h03102.tif,22h03202.tif,22h04102.tif,22h04202.tif,22h06101.tif,22i05101.tif,22i01202.tif,22h16202.tif,22h16201.tif,22h16102.tif,22h16101.tif
(TOO_LONG)



I would like to have a the result in a table of 6 column like this:

22h01101.tif  22h02101.tif  22h02102.tif  22h02202.tif  22h03102.tif  22h03202.tif
22h04102.tif  22h04202.tif  22h06101.tif  22i05101.tif  22i01202.tif  22h16202.tif
22h16201.tif  22h16102.tif  22h16101.tif  22h15202.tif  22h15102.tif  22h09202.tif
22i09102.tif  22i09101.tif  22i08202.tif  22i08201.tif  22i08102.tif  22i08101.tif

etc..

thanks

Roseline Paquin

[Updated on: Thu, 08 October 2009 10:56]

Report message to a moderator

Re: Return result of query into a 6 columns table [message #425323 is a reply to message #425319] Thu, 08 October 2009 10:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you mean by 'How to return the result of my query in a table.'

Do you want to create a table holding the results of your query - Use CREATE TABLE <table_name> AS <query>;

Do you want to return the results of your query into some sort of SQL / PlSql Collection?

Or is it something else entirely?
Re: Return result of query into a 6 columns table [message #425324 is a reply to message #425319] Thu, 08 October 2009 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select stragg2(REGELEMENTS.NOM), regelements.id_disque
from (select r.*, rownum-1 rn from REGELEMENTS r where REGELEMENTS.ID_DISQUE = 10106)
group by ID_Disque, trunc(rn/6)
/

Regards
Michel
Re: Return result of query into a 6 columns table [message #425360 is a reply to message #425324] Thu, 08 October 2009 13:26 Go to previous messageGo to next message
roseline43
Messages: 10
Registered: October 2009
Junior Member
That is exaclty what I was looking for.
Just another question, instead of having the 6 elements separeted with a comma, is it possible to have them separeted in cells?
So all the data will be we aligned. Since I can have about 500 results, it will be better looking.

thanks again,

Roseline
Re: Return result of query into a 6 columns table [message #425362 is a reply to message #425360] Thu, 08 October 2009 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If cell means column this is a different question.
Try:
select regelements.id_disque,
       max(decode(mod(rn,6),0,REGELEMENTS.NOM)) nom,
       max(decode(mod(rn,6),1,REGELEMENTS.NOM)) nom,
       max(decode(mod(rn,6),2,REGELEMENTS.NOM)) nom,
       max(decode(mod(rn,6),3,REGELEMENTS.NOM)) nom,
       max(decode(mod(rn,6),4,REGELEMENTS.NOM)) nom,
       max(decode(mod(rn,6),5,REGELEMENTS.NOM)) nom
from (select r.*, rownum-1 rn from REGELEMENTS r where REGELEMENTS.ID_DISQUE = 10106)
group by ID_Disque, trunc(rn/6)
/

Regards
Michel
Re: Return result of query into a 6 columns table [message #425364 is a reply to message #425360] Thu, 08 October 2009 14:07 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Since I can have about 500 results, it will be better looking.
500 ROWS?
500 COLUMNS?
500 VALUES?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Previous Topic: PL/SQL runtime issue
Next Topic: query audit table (merged 4)
Goto Forum:
  


Current Time: Wed Sep 28 14:13:54 CDT 2016

Total time taken to generate the page: 0.12346 seconds