SQL & PL/SQL :: Convert Column To Rows Tabular Format
Jun 8, 2011
I have job, working hours, employee id, employee name in test_emp table. The job name and employees are not fixed in this table and it varies from project to project. We don't know how many employees are there and needs to be fetching on runtime.
I have the data like below
JOB---------WRKHR---EMPID-----EMPNM
ANALYST-----10------5478------RAMESH
MANAGER-----10------4258------SACHIN
LEAD---------10------4789------VIVEK
DEVELOPER---20------5632------ROHIT
ANALYST-----20------5843------VIRAT
MANAGER-----20------4789------VIVEK
PROGRAMMER-30------5479------SURESH
LEAD---------30------4258------SACHIN
DEVELOPER---30------5231------PRAVEEN
I need the output like below format.
JOB---------RAMESH--SACHIN--VIVEK--ROHIT--VIRAT--SURESH--PRAVEEN--TOTAL
MANAGER-----0--------10-------20-----0-------0------0---------0---------30
LEAD---------0--------30-------10-----0-------0------0---------0---------40
ANALYST-----10-------0---------0-----0------20------0---------0---------30
DEVELOPER---0--------0---------0-----20------0------0---------30--------50
PROGRAMMER-0--------0---------0------0------0------30--------0---------30
--TOTAL-----10-------40--------30-----20-----20-----30--------30--------180
View 6 Replies
ADVERTISEMENT
Mar 8, 2010
how i can convert rows into columns as tabular form.
I have two table : om_supplier
supp_code
supp_name
om_supplier_term
st_supp_code
terms
like if i want to make query form from supp_code to supp_code the output is comming
[code]
select supp_code,terms from om_supplier,om_supplier_term
where supp_Code = st_supp_code
[code]
supp_code terms
1 90days
1 180days
1 120days
i want the output like
supp_code
1 90days-180days-120days
how i can achieve this in form.
View 8 Replies
View Related
Jul 9, 2012
CREATE TABLE EMP(NAME VARCHAR2(10 BYTE))
INSERT INTO EMP VALUES ('C');
INSERT INTO EMP VALUES ('A');
INSERT INTO EMP VALUES ('T');
SELECT xmlelement("NAME",NAME) FROM EMP;
I am trying to convert column data to xml format, but I get this error message:
Quote:The query fails because all columns types are currently not supported. I am using:
Quote:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
SQLTools 1.5.0 Beta build 9 as EDITOR
View 7 Replies
View Related
Sep 25, 2012
How to convert station & date wise multiple rows to date wise column. I have a table TEST like this,
STN_Name Date State
DELHI1-Sep-201G
DELHI2-Sep-201B
DELHI3-Sep-201G
DELHI4-Sep-201G
DELHI5-Sep-201G
DELHI6-Sep-201M
DELHI7-Sep-201G
DELHI8-Sep-201G
DELHI9-Sep-201G
DELHI ......... ..
DELHI 30 Sep 2012 G
KOLKATA1-Sep-201G
KOLKATA2-Sep-201B
KOLKATA3-Sep-201B
KOLKATA4-Sep-201B
KOLKATA5-Sep-201G
KOLKATA6-Sep-201M
KOLKATA7-Sep-201G
KOLKATA8-Sep-201G
KOLKATA9-Sep-201B
I want output like this
STN_Name1-Sep-20122-Sep-20123-Sep-20124-Sep-20125-Sep-2012............Sep 2012
DELHI G B G G G M
KOLKATA G B B B G M
View 2 Replies
View Related
Mar 22, 2012
can we show the following rows into column upto 1000 using sql query?
A
1
2
3
4
[code]...
Result should like this 1,2,3,4,5,6,7,8,...... 1000 including comma(,)
View 3 Replies
View Related
May 10, 2012
SELECT a.objname, c.property1, c.value1
FROM datatable a, datatabledet c
WHERE a.OBID=c.DATAOBID
and a.CLASSNAME='Class1';
OBJNAME PROPERTY1 VALUE1
280-419-1994psCls1Attr3Attr1Value3
280-419-1994psCls1Attr4Attr1Value4
280-419-1994psCls1Attr5Attr1Value5
[code]....
After query output we put through front end code to make it in the following way i.e. convert rows into columns but with respective data value:
psCls1Attr3 psCls1Attr4 psCls1Attr5 psCls1Attr1 psCls1Attr2 psCls1Attr7
280-419-1994 Attr1Value3 Attr1Value4 Attr1Value5 Attr1Value1 Attr1Value2
280-419-1995 Attr1Value11Attr1Value21Attr1Value71
how to output in the above format using SQL.
View 4 Replies
View Related
Nov 20, 2011
how to convert rows data into column...
View 7 Replies
View Related
Jul 3, 2012
I am having a table with following data:
DateProduct_nameQuantity_sold
12/6/2012Product11
12/7/2012product23
12/6/2012product34
12/7/2012Product12
12/7/2012product21
12/6/2012product35
12/7/2012product33
12/6/2012product24
Now I want this data in following manner:
For each date - how much is the Total quantity sold , sum of Product1 sold,Sum of product2 sold,etc. I am having more than 50 products in my table.
dateQuantity_soldProduct1Product2Product3
12/6/2012141 49
12/7/201292 43
I was trying this by decode but the sum of the product was not correct.
View 2 Replies
View Related
Dec 11, 2009
I need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30));
insert into email_tbl('1@y.com');
insert into email_tbl('2@y.com');
insert into email_tbl('3@y.com');
insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
View 7 Replies
View Related
Dec 11, 2012
I have a table where i want the output to be a single column
ex: select from t1;*
query output_
rownum col_1
1 8217
2 6037
3 5368
4 5543
5 5232
i would like the result to be : *8217,6037,5368,5543,5232*
i did look on the web but can't find a solution that is easily understood.
View 9 Replies
View Related
Mar 30, 2012
I've a query that sum up data from one table. But I've to convert them into rows.
Summary query result.
COL1 COL2 COL3 COL4
1000 2000 3000 4000
Test Table creation script.
CREATE TABLE TEST_TBL
(
COL1 NUMBER(14,2),
COL2 NUMBER(14,2),
COL3 NUMBER(14,2),
COL4 NUMBER(14,2)
) ;
INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;
Expected result
A1 1000
A2 2000
A3 3000
A4 4000
A1, A2, A3, A4 are hard coded fixed values.
I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.
SELECT 'A1', COL1 FROM TEST_TBL
UNION ALL
SELECT 'A2', COL2 FROM TEST_TBL
UNION ALL
SELECT 'A3', COL3 FROM TEST_TBL
UNION ALL
SELECT 'A4', COL4 FROM TEST_TBL
View 12 Replies
View Related
Aug 24, 2012
By default number that "starts" with a leading zero with decimals - zero is removed. 0.49 => .49
I'm adding for e.g. the mask "FM990D0999". 0.49 => 0.49
Ok this is fixed.But if I have an integer without decimal. 1 => 1.0
If I change the mask to FM990D9999 1=> 1.
I would like to have a zero leading when it's 0.49 but also no decimal when it's an integer => 1
APEX 4.0 with 11g
View 2 Replies
View Related
Oct 25, 2012
When I build simple tabular form in APEX 4.2 it displayed 10 rows per page by default. When I changed that number in Number of Rows field to 15 nothing happens. Again only 10 rows per page were displayed. Next field on the right to Number of Rows is Number of Rows (Item). Tabular form works as expected when I use this field and define number of rows with hidden page item. So workaround issue (bug?) is easy. Is this some kind of bug or I miss something obvious?
View 1 Replies
View Related
Sep 27, 2012
I have a tabular form (report) multi-record. It display only the records 1-15 with pagination to the other pages. When I set the field 'Number of Rows' to 100, it still displays record 1-15 only. I just want to see the complete dataset on one page, without pagination. (There is a filter on it so there will never be more than 100 records displayed)
View 4 Replies
View Related
Jan 7, 2013
I am using the below query to get time stamp along with time zone format.
>>SELECT CURRENT_TIMESTAMP FROM DUAL
Now the requirement i got is i need to get convert the format in DD-MON-YY, HH:MM:SS, Timezone (GMT or PST)
how to convert this format. Or is there any alternative time stamp option to do conversion.
View 5 Replies
View Related
Oct 18, 2012
I want to convert the date in to a particular format,
1)
2011-03-04T01:45:12
I want to convert the above date value into the below format.
04-MAR-11 01.45.12.000000000 AM
2)
2011-03-04T01:45:12+0006
The above date format into
04-MAR-11 01.45.12.0006 AM
View 5 Replies
View Related
Dec 18, 2012
i have master and detail form. In detail(tabular form) i want to restrict user to enter only 10 rows.if he tries to go for 11th row he shud get a message (Only 10 records can be created at a time).
View 3 Replies
View Related
Oct 11, 2012
i am using one stored procedure where in one variable which is declare as date value is coming like that '10-OCT-12 11.30.54 AM' and i am inserting this value in one table which has one column vdate with date datatype but it is not inserting there.
View 16 Replies
View Related
Jun 12, 2011
I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.
View 3 Replies
View Related
Dec 26, 2011
is it possible convert data from forms 6i to pdf format?
View 4 Replies
View Related
May 30, 2011
convert a HTML to PDF format using plsql using oracle 9i.
View 9 Replies
View Related
Jun 7, 2010
I am having one table in which for currentdate column is having below values
10142
10143
10144
10145
10146
10147
10148
These values represents the date.
Ex 10146 = 146th day of 2010
10147= 147th day of 2010
I want to convert values in currentdate column in yyyymmdd format.
View 3 Replies
View Related
Jan 30, 2013
How i can show the new added rows to tabular form after submit? what I want is to show my customer new added rows that recently added. but according to how I sort the tabular form, the new added rows distributed in multi pagination in tabular form. I'm not good in English, excuse me for my mistakes. I use apex 4.2.0 On Oracle 11g r2 on windows Server 2008
View 2 Replies
View Related
Sep 19, 2012
Is it possible to create a validation for tabular form which will be fired only for created?
There is a possibility in APEX 4.1 to choose two types of "*Execution scope*" first is "*For created and Modified Rows*" and second is "*All Submitted Rows*".
View 3 Replies
View Related
Apr 14, 2011
I want to convert a number to this format using below query but i'm getting error. how to correct the below query.
SELECT TO_CHAR(12345678, '99G9999D99') FROM dual;
error:-###########
View 2 Replies
View Related
Oct 31, 2011
the below request.
My company has many products, whenever customer purchase any product purchase timestamp should be inserted into product_details table .
Note: timestamp of UK, Philippines, Singapore, India time should be inserted automatically as my company is world based company .
I have tried to convert the date format to other(uk,singpore,manalia) timings but i couldn't get proper reply.
SELECT to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') INDIA,
to_char(new_time(sysdate, 'EST', 'GMT'),'DD-MON-YYYY HH:MI:SS AM') D2 ,
to_char(new_time(sysdate, 'PST', 'GMT'),'DD-MON-YYYY HH:MI:SS AM') D3 ,
to_char(new_time(sysdate, 'EST', 'PST'),'DD-MON-YYYY HH:MI:SS AM') D4
FROM dual
I got the below output.
INDIA D2 D3 D4
----------------------- ----------------------- ----------------------- -----------------------
31-OCT-2011 06:06:16 PM 31-OCT-2011 11:06:16 PM 01-NOV-2011 02:06:16 AM 31-OCT-2011 03:06:16 PM
View 9 Replies
View Related
Jun 3, 2010
i have create one standard Calender from that i pickup month date and year separately like 2/6/1987 now i want to convert it into standard date format how to convert it and pass to another block....
View 3 Replies
View Related
Oct 5, 2010
I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.
View 5 Replies
View Related
Sep 20, 2012
I am using oracle database 9iR2 (9.2.0.8) on windows 2003 server.
Is there any method to convert following output in to ASCII format ??
select ename from scott.emp;
ENAME
--------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
View 6 Replies
View Related
Nov 10, 2012
i m using oracle 9i (9.2.0.8.0) & developer 2000..I am getting below mentioned error when i convert Oracle Report to excel format.
"Report Builder
REP-1401:'afterreport':Fatal PL/SQL
error occurred ORA-43356 : Message 43356
not found; product = RDBMS ; facility = ORA "
View 5 Replies
View Related
