Home » SQL & PL/SQL » SQL & PL/SQL » Converting columns into rows
Converting columns into rows [message #441242] Fri, 29 January 2010 12:51 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Converting columns into rows [message #441250 is a reply to message #441249] Fri, 29 January 2010 14:38 Go to previous message
sean1970
Messages: 13
Registered: January 2010
Junior Member
Thanks a lot Michel, this is the output i was looking for. Using this i can write so many other queries.
Previous Topic: Query
Next Topic: ORA-19025 on Binary XML Table Unique Constraint (merged by CM)
Goto Forum:
  


Current Time: Sat Dec 03 18:04:02 CST 2016

Total time taken to generate the page: 0.11463 seconds