Home » SQL & PL/SQL » SQL & PL/SQL » Converting columns into rows
| Converting columns into rows [message #441242] |
Fri, 29 January 2010 12:51  |
sean1970
Messages: 13
Registered: January 2010
|
Junior Member |
|
|
Hi,
I need to write a query which will convert the columns into rows.
SELECT t1.name, t1.start_date, t1.end_date,t2.activity,
t2.day1,t2.day2,t2.day3,t2.day4,t2.day5,t2.day6,t2.day7,t2.day8,t2.day9,t2.day10,
t2.day11,t2.day12,t2.day13,t2.day14,t2.day15,t2.day16,t2.day17,t2.day18,t2.day19,t2.day20,
t2.day21,t2.day22,t2.day23,t2.day24,t2.day25,t2.day26,t2.day27,t2.day28,t2.day29,t2.day30,
t2.day31
FROM master t1, details t2
WHERE t1.id = t2.id
The above query will show the output something like this....
Name Start End Date Actv. Day1 Day2 Day3 Till Day31
-----------------------------------------------------------------
user1 1/1/2010 01/31/2010 Acti1 8 8 0 ....
user1 1/1/2010 01/31/2010 Acti2 8 8 0 ....
user2 1/1/2010 01/31/2010 Acti3 8 8 0 ....
user2 1/1/2010 01/31/2010 Acti4 8 8 0 ....
Expected Output......
Name Date Activity Time
--------------------------------------------------
user1 1-01-2010 Acti1 8
user1 2-01-2010 Acti1 8
user1 1-01-2010 Acti2 8
user1 2-01-2010 Acti2 8
user2 1-01-2010 Acti3 8
user2 2-01-2010 Acti3 8
user2 1-01-2010 Acti4 8
user2 2-01-2010 Acti4 8
Can anyone please give some e.g or suggestion how i can get the output in above format.
Thanks in Advance
|
|
|
|
| Re: Converting columns into rows [message #441243 is a reply to message #441242] |
Fri, 29 January 2010 13:19  |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Clue: you have to use a row generator.
If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
Regards
Michel
[Updated on: Fri, 29 January 2010 13:20] Report message to a moderator |
|
|
|
| Re: Converting columns into rows [message #441244 is a reply to message #441242] |
Fri, 29 January 2010 13:26 |
|
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 data as (
3 select 1 id, 11 col1, 12 col2, 13 col3 from dual
4 union all
5 select 2, 21, 22, 23 from dual
6 ),
7 lines as (select level line from dual connect by level <= 3)
8 select id,
9 decode(line,1,col1,2,col2,3,col3) val
10 from data, lines
11 order by id, val
12 /
ID VAL
---------- ----------
1 11
1 12
1 13
2 21
2 22
2 23
6 rows selected.
Regards
Michel
|
|
|
|
| Re: Converting columns into rows [message #441248 is a reply to message #441243] |
Fri, 29 January 2010 14:15 |
sean1970
Messages: 13
Registered: January 2010
|
Junior Member |
|
|
Here is the code.....
CREATE TABLE time_master
(
col_id VARCHAR2(100 CHAR),
username VARCHAR2(100 CHAR),
ts_period VARCHAR2(10 CHAR),
ts_start DATE,
ts_end DATE
);
CREATE TABLE time_details
(
col_id VARCHAR2(100 CHAR),
act_code VARCHAR2(10 CHAR),
dd1 NUMBER(4,1),
dd2 NUMBER(4,1),
dd3 NUMBER(4,1),
dd4 NUMBER(4,1),
dd5 NUMBER(4,1),
dd6 NUMBER(4,1),
dd7 NUMBER(4,1),
dd8 NUMBER(4,1),
dd9 NUMBER(4,1),
dd10 NUMBER(4,1),
dd11 NUMBER(4,1),
dd12 NUMBER(4,1),
dd13 NUMBER(4,1),
dd14 NUMBER(4,1),
dd15 NUMBER(4,1),
dd16 NUMBER(4,1),
dd17 NUMBER(4,1),
dd18 NUMBER(4,1),
dd19 NUMBER(4,1),
dd20 NUMBER(4,1),
dd21 NUMBER(4,1),
dd22 NUMBER(4,1),
dd23 NUMBER(4,1),
dd24 NUMBER(4,1),
dd25 NUMBER(4,1),
dd26 NUMBER(4,1),
dd27 NUMBER(4,1),
dd28 NUMBER(4,1),
dd29 NUMBER(4,1),
dd30 NUMBER(4,1),
dd31 NUMBER(4,1)
);
INSERT INTO time_master
(col_id, username, ts_period, ts_start,
ts_end
)
VALUES ('1', 'user1', 'Jan-2010', TO_DATE ('01/01/2010', 'MM/DD/YYYY'),
TO_DATE ('01/31/2010', 'MM/DD/YYYY')
);
INSERT INTO time_details
(col_id, act_code, dd1, dd2, dd3, dd4, dd5, dd6, dd7, dd8, dd9,
dd10, dd11, dd12, dd13, dd14, dd15, dd16, dd17, dd18, dd19,
dd20, dd21, dd22, dd23, dd24, dd25, dd26, dd27, dd28, dd29,
dd30, dd31
)
VALUES ('1', 'Act-001', 8, 8, 8, 8, 8, 0, 0, 6, 8,
8, 7, 8, 0, 0, 8, 8, 8, 8, 0,
0, 0, 8, 8, 7, 8, 8, 0, 0, 6,
5, 8
);
INSERT INTO time_details
(col_id, act_code, dd1, dd2, dd3, dd4, dd5, dd6, dd7, dd8, dd9,
dd10, dd11, dd12, dd13, dd14, dd15, dd16, dd17, dd18, dd19,
dd20, dd21, dd22, dd23, dd24, dd25, dd26, dd27, dd28, dd29,
dd30, dd31
)
VALUES ('1', 'Act-002', 8, 8, 8, 8, 8, 0, 0, 7, 8,
8, 7, 8, 0, 0, 8, 8, 8, 8, 0,
0, 0, 8, 8, 7, 8, 8, 0, 0, 6,
5, 8
);
COMMIT;
drop table time_master;
drop table time_details;
--I need the output in this format...
Username Activity Date Time Entered
----------------------------------------------
user1 Act-001 01/01/2010 8
user1 Act-002 01/01/2010 8
user1 Act-001 02/01/2010 8
user1 Act-002 02/01/2010 8
user1 Act-001 03/01/2010 8
user1 Act-002 03/01/2010 8
user1 Act-001 04/01/2010 8
user1 Act-002 04/01/2010 8
user1 Act-001 05/01/2010 8
user1 Act-002 05/01/2010 8
user1 Act-001 08/01/2010 6
user1 Act-002 08/01/2010 7
user1 Act-001 09/01/2010 8
user1 Act-002 09/01/2010 8
.....Till 31/01/2010
My oracle version is 10.2.0.3
Thanks
|
|
|
|
| Re: Converting columns into rows [message #441249 is a reply to message #441248] |
Fri, 29 January 2010 14:29 |
|
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 lines as (select level line from dual connect by level <= 31)
3 select m.username, d.act_code, m.ts_start+line-1 "Date",
4 decode(line, 1,dd1, 2,dd2, 3,dd3, 4,dd4, 5,dd5, 6,dd6, 7,dd7, 8,dd8, 9,dd9,
5 10,dd10, 11,dd11, 12,dd12, 13,dd13, 14,dd14, 15,dd15, 16,dd16, 17,dd17, 18,dd8, 19,dd19,
6 20,dd20, 21,dd21, 22,dd22, 23,dd23, 24,dd24, 25,dd25, 26,dd26, 27,dd27, 28,dd8, 29,dd29,
7 30,dd30, 31,dd31) "Time"
8 from time_master m, time_details d, lines
9 where m.col_id = d.col_id
10 order by 1, 3, 2
11 /
USERNAME ACT_CODE Date Time
---------- ---------- ----------- ----------
user1 Act-001 01-JAN-2010 8
user1 Act-002 01-JAN-2010 8
user1 Act-001 02-JAN-2010 8
user1 Act-002 02-JAN-2010 8
user1 Act-001 03-JAN-2010 8
user1 Act-002 03-JAN-2010 8
user1 Act-001 04-JAN-2010 8
user1 Act-002 04-JAN-2010 8
user1 Act-001 05-JAN-2010 8
user1 Act-002 05-JAN-2010 8
user1 Act-001 06-JAN-2010 0
user1 Act-002 06-JAN-2010 0
user1 Act-001 07-JAN-2010 0
user1 Act-002 07-JAN-2010 0
user1 Act-001 08-JAN-2010 6
user1 Act-002 08-JAN-2010 7
user1 Act-001 09-JAN-2010 8
user1 Act-002 09-JAN-2010 8
user1 Act-001 10-JAN-2010 8
user1 Act-002 10-JAN-2010 8
user1 Act-001 11-JAN-2010 7
user1 Act-002 11-JAN-2010 7
user1 Act-001 12-JAN-2010 8
user1 Act-002 12-JAN-2010 8
user1 Act-001 13-JAN-2010 0
user1 Act-002 13-JAN-2010 0
user1 Act-001 14-JAN-2010 0
user1 Act-002 14-JAN-2010 0
user1 Act-001 15-JAN-2010 8
user1 Act-002 15-JAN-2010 8
user1 Act-001 16-JAN-2010 8
user1 Act-002 16-JAN-2010 8
user1 Act-001 17-JAN-2010 8
user1 Act-002 17-JAN-2010 8
user1 Act-001 18-JAN-2010 6
user1 Act-002 18-JAN-2010 7
user1 Act-001 19-JAN-2010 0
user1 Act-002 19-JAN-2010 0
user1 Act-001 20-JAN-2010 0
user1 Act-002 20-JAN-2010 0
user1 Act-001 21-JAN-2010 0
user1 Act-002 21-JAN-2010 0
user1 Act-001 22-JAN-2010 8
user1 Act-002 22-JAN-2010 8
user1 Act-001 23-JAN-2010 8
user1 Act-002 23-JAN-2010 8
user1 Act-001 24-JAN-2010 7
user1 Act-002 24-JAN-2010 7
user1 Act-001 25-JAN-2010 8
user1 Act-002 25-JAN-2010 8
user1 Act-001 26-JAN-2010 8
user1 Act-002 26-JAN-2010 8
user1 Act-001 27-JAN-2010 0
user1 Act-002 27-JAN-2010 0
user1 Act-001 28-JAN-2010 6
user1 Act-002 28-JAN-2010 7
user1 Act-001 29-JAN-2010 6
user1 Act-002 29-JAN-2010 6
user1 Act-001 30-JAN-2010 5
user1 Act-002 30-JAN-2010 5
user1 Act-001 31-JAN-2010 8
user1 Act-002 31-JAN-2010 8
62 rows selected.
Regards
Michel
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jul 09 17:38:33 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
