Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Lees Summit MO
    Posts
    3

    Unanswered: Crosstab or Pivot SQL Statement

    Hi all,

    I am hoping there is a way to do this with a SQL statement. I am new to Oracle and to this forum so please be patient.

    This is a report for a warehouse environment.

    I need to convert this:
    Code:
    Item	Location			
    1234	A			
    1234	B			
    1234	C			
    4567	D			
    4567	E			
    5678	F			
    5678	G			
    5678	H			
    5678	I
    Into this:
    Code:
    				
    Item	LOC1	LOC2	LOC3	LOC4
    1234	A	B	C	NULL
    4567	D	E	NULL	NULL
    5678	F	G	H	I
    Is this even possible? I have searched and read endless amounts of post and web pages over the past two days. I am not even sure what version of Oracle our data warehouse is using. I do know I cannot use PIVOT in my SQL statement. I have been left with these Oracle responsibilities and have to learn on the fly. My past 8 years of experience has all been with Microsoft products.

    I am using TOAD 9.1 just in case it matters and I have no experience at all using PL/SQL.

    All advice/help is greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    For pivoting, you just need to determine under which LOC column shall the given LOCATION be displayed. Using analytic ROW_NUMBER function shall lead to it. For example, if the locations shall be sequenced alphabetically, the formula for determining LOC column would be this:
    Code:
    ROW_NUMBER() OVER (PARTITION BY item ORDER BY location)
    The rest would be "standard" pivoting, as described e.g. in this article: http://www.oracle.com/technetwork/is...om-094550.html (there are some other things too, just search for "pivot").

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Oct 2010
    Location
    Lees Summit MO
    Posts
    3
    Thanks very much everyone for taking time to respond!

    The two links you supplied plus a response from one of my co-workers gives me exactly what I need. I did actually see both of those threads in my searching yesterday but my inexperience made it difficult for me to understand.

    For those searching for this solution with my level of limited experience here is a solution that helped me clearly understand and is now working for me:


    Step 1 : First Create table in your schema.
    Code:
    create table .pivot_tbl (item varchar2(30),Location varchar2(10));
    Step 2: Insert the dummy record in the table. Please run the below SQL statements.

    SQL :
    Code:
    insert into pivot_tbl values(1234,'A');                           
    insert into pivot_tbl values(1234,'B');                         
    insert into pivot_tbl values(1234,'C');                         
    insert into pivot_tbl values(4567,'D');                          
    insert into pivot_tbl values(4567,'E');                           
    insert into pivot_tbl values(5678,'F');                           
    insert into pivot_tbl values(5678,'G');                           
    insert into pivot_tbl values(5678,'H');                           
    insert into pivot_tbl values(5678,'I');
    Step 3: The below query will show the data based on your requirement.
    Code:
    SELECT   
        item, 
        MAX (a_location) a_buss_location,
        MAX (b_location) b_buss_location, 
        MAX (c_location) c_buss_location,
        MAX (d_location) d_buss_location,
        MAX (e_location) e_buss_location,
        MAX (f_location) f_buss_location
    FROM
    (
    SELECT 
        item,
        DECODE (rn1, 1, location, NULL) a_location,
        DECODE (rn1, 2, location, NULL) b_location,
        DECODE (rn1, 3, location, NULL) c_location,
        DECODE (rn1, 4, location, NULL) d_location,
        DECODE (rn1, 5, location, NULL) e_location,
        DECODE (rn1, 6, location, NULL) f_location
    FROM
    (SELECT 
        item,location,
        ROW_NUMBER () OVER (PARTITION BY item ORDER BY location) rn1,
        ROW_NUMBER () OVER (PARTITION BY item ORDER BY location) rn2,
        ROW_NUMBER () OVER (PARTITION BY item ORDER BY location) rn3,
        ROW_NUMBER () OVER (PARTITION BY item ORDER BY location) rn4,
        ROW_NUMBER () OVER (PARTITION BY item ORDER BY location) rn5,
        ROW_NUMBER () OVER (PARTITION BY item ORDER BY location) rn6
    FROM 
    (select 
        item,
        location 
    from 
        pivot_tbl
    group by 
        item,
        location 
    order by 1)))
    group by 
        item
    I had to add more columns to make it work for my situation. I also could not create the table in my schema due to privileges or my lack of experience with the software.

    Thanks Again ALL!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •