Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    34

    Unanswered: Please help with this Query

    Hi ALL,

    My requirement with one table which contains 20 columns. Now I want to convert 18 column into rows. I tried with the following query but its throwing sql error is given below.


    select * from tmpl_pop_age_range_col_lvl
    UNPIVOT
    (
    quantity FOR product IN (COUNT_0_TO_4 as 'a', COUNT_5_TO_9 as 'b', COUNT_10_TO_14 as 'c',
    COUNT_15_TO_19 as 'd',
    COUNT_20_TO_24 as 'e',
    COUNT_25_TO_29 as 'f',
    COUNT_30_TO_34 as 'g',
    COUNT_35_TO_39 as 'h',
    COUNT_40_TO_44 as 'i',
    COUNT_45_TO_49 as 'j',
    COUNT_50_TO_54 as 'k',
    COUNT_55_TO_59 as 'l',
    COUNT_60_TO_64 as 'm',
    COUNT_65_TO_69 as 'n',
    COUNT_70_TO_74 as 'o',
    COUNT_75_TO_79 as 'p',
    COUNT_80_TO_84 as 'q',
    COUNT_OVER_85 as 'r')
    );

    *
    ERROR at line 4:
    ORA-00933: SQL command not properly ended

    I would appreciate your earliest responses,
    Thanks,

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    the syntax seems correct, just two ideas:
    - What is your database version? UNPIVOT is supported from 11g. Although, it throws error right after UNPIVOT word (line 2) in 10gR2 for me.
    - The called statement may contain some "special" characters, e.g. hard spaces. The copied text does not seem to contain them, anyway, you should check the line 4/5 in the real statement.

  3. #3
    Join Date
    Aug 2010
    Posts
    34
    Thanks for the prompt response, The oracle version is 10.2.0.4.
    Is any other it can be sorted out. I am furnishing some more details that
    may help,


    The Table DDL

    CREATE TABLE TMPL_POP_AGE_RANGE_COL_LVL
    (
    YEAR NUMBER,
    SEX VARCHAR2(100 BYTE),
    COUNT_0_TO_4 NUMBER,
    COUNT_5_TO_9 NUMBER,
    COUNT_10_TO_14 NUMBER,
    COUNT_15_TO_19 NUMBER,
    COUNT_20_TO_24 NUMBER,
    COUNT_25_TO_29 NUMBER,
    COUNT_30_TO_34 NUMBER,
    COUNT_35_TO_39 NUMBER,
    COUNT_40_TO_44 NUMBER,
    COUNT_45_TO_49 NUMBER,
    COUNT_50_TO_54 NUMBER,
    COUNT_55_TO_59 NUMBER,
    COUNT_60_TO_64 NUMBER,
    COUNT_65_TO_69 NUMBER,
    COUNT_70_TO_74 NUMBER,
    COUNT_75_TO_79 NUMBER,
    COUNT_80_TO_84 NUMBER,
    COUNT_OVER_85 NUMBER
    );

    No Data in this Table Yet
    My Expected Outcome should look like this,

    From this format,
    ---------------------
    Year Sex Count_0_to_4 Count_5_to_9
    2010 F 100 200


    To something to look like this,
    --------------------------------
    Year Sex Count Age Range
    2010 F 100 0 - 4
    2010 F 200 5 - 9



    Any Suggestions Please,
    Thanks,
    Last edited by mr10; 08-09-10 at 10:11.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down What did you not understand?

    As flyboy indicated: "UNPIVOT is supported from 11g" which means there is no PIVOT/UNPIVOT functionality in 10g.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2010
    Posts
    34
    Yeah, you are right ... I too figured out from the google bit later on though, Thanx Anyway, Someone has suggested this & it looks fine to me.


    select year, sex, age_range,
    decode( age_range, '0-4', COUNT_0_TO_4,
    '5-9', COUNT_5_TO_9,
    <add other columns> ) cnt
    from TMPL_POP_AGE_RANGE_COL_LVL
    cross join ( select '0-4' age_range from dual
    union all select '5-9' from dual
    <add other column descriptions> );



    Regards,

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Someone has suggested this & it looks fine to me.
    Looks can be deceiving.
    Does it produce desired results?
    SQL always produces results, but it might be the answer to the desired question.
    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.

Posting Permissions

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