5 Replies Latest reply: Nov 16, 2012 9:43 AM by 974533 RSS

    Need a pseudo cross tab query

    974533
      Not sure how to do this. I have a table with contents as

      empid
      empnm
      office
      phone

      and another table with

      empid
      workdt
      notin

      I need to display the data such as

      empid empnm office phone 1/1/12 1/2/12 1/3/12 1/4/12 1/5/12 1/6/12 1/7/12

      123 joe 123 x x x
      467 sam 333
      777 bill 444 x x

      The data in the employee file looks like this

      empid empnm office phone
      123 joe 123
      467 sam 333
      777 bill 444

      The data in the workdates file looks like this

      empid workdt notin
      123 1/1/12 x
      123 1/2/12
      123 1/3/12 x
      123 1/4/12 x
      123 1/5/12
      123 1/6/12
      123 1/7/12
      467 1/1/12
      467 1/2/12
      467 1/3/12
      467 1/4/12
      467 1/5/12
      467 1/6/12
      467 1/7/12
      777 1/1/12
      777 1/2/12 x
      777 1/3/12
      777 1/4/12
      777 1/5/12 x
      777 1/6/12
      777 1/7/12
        • 1. Re: Need a pseudo cross tab query
          sb92075
          why do you post in forum for SQL & PL/SQL yet never include a single line of either language?


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Need a pseudo cross tab query
            Billy~Verreynne
            user8817486 wrote:
            Not sure how to do this. I have a table with contents as
            and another table with
            You need to describe the tables better than this - ideally supply the DDLs and a couple of insert statements to populate the tables with the relevant test data.
            I need to display the data such as
            If it is a display issue, why then does the client not deal with this? The client is responsible for rendering server data to the user. And if that rendered output is in a specific report format, then the client should format the server output in that format.

            So why do you want to do this formatting on the server side instead? What will this achieve or solve?
            The data in the employee file looks like this
            What file? There are tables in the database. Not files. What are you referring to?

            If you think a database table is like a file, then you are sorely mistaken. ISAM based file-and-record processing dates back to the 80's and have absolutely NOTHING in common with the modern RDBMS. Treat the RDBMS as if it contains files and records and you will do serious damage ito performance, flexibility and scalability. In which case you can instead just as well forget about using the RDBMS all together and use actual real files-and-records instead.

             
            As <i>sb</i>'s response indicated - please ask questions properly. It is impossible to make heads or tails out of a problem when it lacks details that accurately describe the problem.
            • 3. Re: Need a pseudo cross tab query
              974533
              First of all, I know the difference between a file and table. If you look, you will see that I stated the "Tables" at the top. I referred to the data in "Files" because I am importing it from a flat file into the "Tables". No need to get bent out of shape about something so trivial. Secondly, I need it displayed like this at the client side because the user wants to see 1 row per employee with each of the dates as columns at the top to view in a cross tab format whether the person was in on that day. But to answer your questions see below.

              describe empdef;

              empid number
              empnm varchar2(30)
              office varchar2(30)
              phone varchar2(10)

              describe workdata;

              empid number
              workdt date
              notin varchar2(1)

              I need to display the data such as

              empid empnm office phone 1/1/12 1/2/12 1/3/12 1/4/12 1/5/12 1/6/12 1/7/12

              123 joe 123 x x x
              467 sam 333
              777 bill 444 x x

              As I said I'm loading the values from a flat file, but if you want insert statements then they are as follows:

              insert into empdef (empid,empnm,offic, phone) values (123,'Joe','123',null);
              insert into empdef (empid,empnm,offic, phone) values (467,'Sam','333',null);
              insert into empdef (empid,empnm,offic, phone) values (777,'Bill','444',null);



              insert into workdata (empid workdt notin) values (123,'01-JAN-12','X');
              insert into workdata (empid workdt notin) values (123,'02-JAN-12',null);
              insert into workdata (empid workdt notin) values (123,'03-JAN-12','X');
              insert into workdata (empid workdt notin) values (123,'04-JAN-12','X');
              insert into workdata (empid workdt notin) values (123,'05-JAN-12',null);
              insert into workdata (empid workdt notin) values (123,'06-JAN-12,null);
              insert into workdata (empid workdt notin) values (123,'07-JAN-12,null);
              insert into workdata (empid workdt notin) values (467,'01-JAN-12',null);
              insert into workdata (empid workdt notin) values (467,'02-JAN-12',null);
              insert into workdata (empid workdt notin) values (467,'03-JAN-12',null);
              insert into workdata (empid workdt notin) values (467,'04-JAN-12',null);
              insert into workdata (empid workdt notin) values (467,'05-JAN-12',null);
              insert into workdata (empid workdt notin) values (467,'06-JAN-12,null);
              insert into workdata (empid workdt notin) values (467,'07-JAN-12,null);
              insert into workdata (empid workdt notin) values (777,'01-JAN-12',null);
              insert into workdata (empid workdt notin) values (777 ,'02-JAN-12','X');
              insert into workdata (empid workdt notin) values (777,'03-JAN-12',null);
              insert into workdata (empid workdt notin) values (777,'04-JAN-12',null);
              insert into workdata (empid workdt notin) values (777,'05-JAN-12','X');
              insert into workdata (empid workdt notin) values (777,'06-JAN-12,null);
              insert into workdata (empid workdt notin) values (777,'07-JAN-12,null);




              commit;

              I put a commit statement in there just you wont bust my chops again stating the data is not actually in the tables because the data is not committed.
              • 4. Re: Need a pseudo cross tab query
                Peter vd Zwan
                Hi,

                There are more different options but I show two below:
                with empdef as
                (
                select 123 empid, 'Joe' empnm,  '123' office, cast(null as varchar2(10)) phone from dual union all
                select 467,       'Sam',        '333',        null from dual union all
                select 777,       'Bill',       '444',        null from dual 
                )
                ,workdata as
                (
                select 123 empid, to_date('01-JAN-12', 'dd-mon-yy') workdt,  'X' notin from dual union all
                select 123, to_date('02-JAN-12', 'dd-mon-yy'),null from dual union all
                select 123, to_date('03-JAN-12', 'dd-mon-yy'),'X' from dual union all
                select 123, to_date('04-JAN-12', 'dd-mon-yy'),'X' from dual union all
                select 123, to_date('05-JAN-12', 'dd-mon-yy'),null from dual union all
                select 123, to_date('06-JAN-12', 'dd-mon-yy'),null from dual union all
                select 123, to_date('07-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('01-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('02-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('03-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('04-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('05-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('06-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('07-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('01-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('02-JAN-12', 'dd-mon-yy'),'X' from dual union all
                select 777, to_date('03-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('04-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('05-JAN-12', 'dd-mon-yy'),'X' from dual union all
                select 777, to_date('06-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('07-JAN-12', 'dd-mon-yy'),null from dual
                )
                
                select
                  e.empid
                  ,e.empnm
                  ,e.office
                  ,e.phone
                  ,max(case when w.workdt = to_date('01-01-2012', 'dd-mm-yyyy') then notin else null end) "1/1/12"
                  ,max(case when w.workdt = to_date('02-01-2012', 'dd-mm-yyyy') then notin else null end) "1/2/12"
                  ,max(case when w.workdt = to_date('03-01-2012', 'dd-mm-yyyy') then notin else null end) "1/3/12"
                  ,max(case when w.workdt = to_date('04-01-2012', 'dd-mm-yyyy') then notin else null end) "1/4/12"
                  ,max(case when w.workdt = to_date('05-01-2012', 'dd-mm-yyyy') then notin else null end) "1/5/12"
                  ,max(case when w.workdt = to_date('06-01-2012', 'dd-mm-yyyy') then notin else null end) "1/6/12"
                  ,max(case when w.workdt = to_date('07-01-2012', 'dd-mm-yyyy') then notin else null end) "1/7/12"
                  
                from
                  empdef        e     join
                  workdata     w     on (e.empid     = w.empid)
                
                group by
                  e.empid
                  ,e.empnm
                  ,e.office
                  ,e.phone
                
                
                order by
                  e.empid
                ;
                EMPID EMPNM OFFICE PHONE      1/1/12 1/2/12 1/3/12 1/4/12 1/5/12 1/6/12 1/7/12
                ----- ----- ------ ---------- ------ ------ ------ ------ ------ ------ ------
                  123 Joe   123               X             X      X                           
                  467 Sam   333                                                                
                  777 Bill  444                      X                    X                    
                or with the pivot clause:
                with empdef as
                (
                select 123 empid, 'Joe' empnm,  '123' office,  cast(null as varchar2(10)) phone from dual union all
                select 467,       'Sam',        '333',        null from dual union all
                select 777,       'Bill',       '444',        null from dual 
                )
                ,workdata as
                (
                select 123 empid, to_date('01-JAN-12', 'dd-mon-yy') workdt,  'X' notin from dual union all
                select 123, to_date('02-JAN-12', 'dd-mon-yy'),null from dual union all
                select 123, to_date('03-JAN-12', 'dd-mon-yy'),'X' from dual union all
                select 123, to_date('04-JAN-12', 'dd-mon-yy'),'X' from dual union all
                select 123, to_date('05-JAN-12', 'dd-mon-yy'),null from dual union all
                select 123, to_date('06-JAN-12', 'dd-mon-yy'),null from dual union all
                select 123, to_date('07-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('01-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('02-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('03-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('04-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('05-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('06-JAN-12', 'dd-mon-yy'),null from dual union all
                select 467, to_date('07-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('01-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('02-JAN-12', 'dd-mon-yy'),'X' from dual union all
                select 777, to_date('03-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('04-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('05-JAN-12', 'dd-mon-yy'),'X' from dual union all
                select 777, to_date('06-JAN-12', 'dd-mon-yy'),null from dual union all
                select 777, to_date('07-JAN-12', 'dd-mon-yy'),null from dual
                )
                
                select
                  *    
                
                from
                  empdef        e     join
                  workdata      w     using (empid)
                
                pivot(max(notin) for workdt in (to_date( '01-01-2012', 'mm-dd-yyyy') as "1/1/12" 
                                                ,to_date( '01-02-2012', 'mm-dd-yyyy') as "1/2/12" 
                                                ,to_date( '01-03-2012', 'mm-dd-yyyy') as "1/3/12" 
                                                ,to_date( '01-04-2012', 'mm-dd-yyyy') as "1/4/12" 
                                                ,to_date( '01-05-2012', 'mm-dd-yyyy') as "1/5/12" 
                                                ,to_date( '01-06-2012', 'mm-dd-yyyy') as "1/6/12" 
                                                ,to_date( '01-07-2012', 'mm-dd-yyyy') as "1/7/12" 
                                                )
                      )
                ;
                
                EMPID EMPNM OFFICE PHONE      1/1/12 1/2/12 1/3/12 1/4/12 1/5/12 1/6/12 1/7/12
                ----- ----- ------ ---------- ------ ------ ------ ------ ------ ------ ------
                  467 Sam   333                                                                
                  123 Joe   123               X             X      X                           
                  777 Bill  444                      X                    X                    
                Regards,

                Peter
                • 5. Re: Need a pseudo cross tab query
                  974533
                  Awsome! Works perfectly. Thanks a ton.