Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Every Row into an individual Columns in SQL Query

534103Jan 15 2010 — edited May 15 2010
Hi Experts,
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production          
PL/SQL Release 11.1.0.7.0 - Production                                          
CORE	11.1.0.7.0	Production                                                      
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production                         
NLSRTL Version 11.1.0.7.0 - Production          
-- DROP TABLE RENDER_VALUES;
CREATE TABLE RENDER_VALUES
(
    ID          INTEGER PRIMARY KEY,
    FIELD_NAME  VARCHAR2(1000),
    FIELD_VALUE VARCHAR2(1000)
);
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(1,'CropX','10.31234');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(2,'CropY','20.31234');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(3,'Height','100');
INSERT INTO RENDER_VALUES(ID,FIELD_NAME,FIELD_VALUE)VALUES(4,'Width','200');
COMMIT;
SELECT FIELD_NAME,FIELD_VALUE FROM RENDER_VALUES;
FIELD_NAME	FIELD_VALUE
CropX		10.31234
CropY		20.31234
Height		100
Width		200
I need the output as:
CropX		CropY		Height	Width
10.31234	20.31234	100	200
Just for sample ..i have given Four Values in Field name Column...there can be (n) no.of values in that and to get each row
into an individual columns...how can i do something like this?

- Dharan V
This post has been answered by Frank Kulash on Jan 15 2010
Jump to Answer

Comments

Karthick2003

First step can you describe TABLE1 and TABLE2?

2611484

I already edit the question

Karthick2003 
Describe means like this

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER
 ENAME                                              VARCHAR2(6)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER
 HIREDATE                                           DATE
 SAL                                                NUMBER
 COM                                                NUMBER
 DEPTNO                                             NUMBER

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER
 DNAME                                              VARCHAR2(10)
 LOC                                                VARCHAR2(8)

SQL>
2611484

I posted the desc of table

Karthick2003

>  and c.status like 'ANSWERED%'

I dont see STATUS column in Table2

2611484

sorry i accidentally erase, now there's a status table

Karthick2003

Ok got it, Change this

select 'Data'
       ||','||to_char(d.dtime_day,'MM/dd/yyyy')
       ||','||sg1_cnt

To

select 'Data'
       ||','||dtime_day 


       ||','||sg1_cnt

dtime_day is already converted in the inner query.

2611484

i changed it already but i got zero results only, which is not right. each item have large total of result

Karthick2003

> i changed it already

Should I use my crystal ball to find it out?

> but i got zero results only, which is not right. each item have large total of result

Without your data i cant do much.

Moazzam 
select 'Data'


||','||to_char(d.dtime_day,'MM/dd/yyyy')


||','||sg1_cnt


||','||sg2_cnt


||','||sg3_cnt


||','||sg4_cnt


||','||sg5_cnt


||','||sg6_cnt


||','||sg7_cnt


||','||sg8_cnt


||','||sg9_cnt


||','||sg10_cnt

In the above select statement, which tables contains the columns (sg1_cnt, sg2_cnt, sg3_cnt.....etc), I think by removing these columns from SELECT shall fix the issue.

Karthick2003

Those come from the PIVOT. That is fine.

2611484

i change the sql script, but i got still zero results

Karthick2003 
33795304-702b-48ff-b4ba-e896b1db94db wrote:




i change the sql script, but i got still zero results

That means some of your conditions are failing. As said already without knowing what's in the table cant help. Just check you join and where conditions.

padders

Just to be clear, are you asking us to tell you why a query we haven't seen returns no rows based on data we haven't seen?

1 person found this helpful
2611484

I just posted some data of each table coumn

2611484

I already edited my post - sql sript and added more info about table columns

1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 12 2010
Added on Jan 15 2010
19 comments
7,065 views