Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Question Unanswered: SQL Query - help required - possibly transpose??

    For the attached Table structure, I am not able to build the required output, not sure if I need to apply transpose, of which I don't have a strong knowledge.
    Please have a look at the jpg attachment. Mentioned towards the end is the SQL script for table creation and data insertion.

    Output should be in the format as shown in the jpg attachment and ID_MAX_VAL of output result should be the max of ID for each manufacturer-country combination in main source table. I need to fetch the max id for each manufacturer-country combination and display/use them to send out a report.

    Note: This is a test data and table structure to simulate the actual business requirement.

    A view similar to the table I have mentioned is the only thing that we have access to and that works as our sole source. Have to work with that only. Need your help in forming the query to obtain the desired output.

    Script:
    Code:
    CREATE TABLE TB_TEST_01 
    (
      ID NUMBER(6) NOT NULL 
    , PARAM_NM VARCHAR2(200) NOT NULL 
    , PARAM_VAL VARCHAR2(200) 
    );
    /
    
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (10, 'MANUFACTURER', 'NISSAN');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (10, 'COUNTRY', 'USA');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (30, 'MANUFACTURER', 'NISSAN');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (30, 'COUNTRY', 'UK');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (20, 'MANUFACTURER', 'NISSAN');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (20, 'COUNTRY', 'UK');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (50, 'MANUFACTURER', 'HONDA');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (50, 'COUNTRY', 'USA');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (60, 'MANUFACTURER', 'HONDA');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (60, 'COUNTRY', 'USA');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (80, 'MANUFACTURER', 'HONDA');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (80, 'COUNTRY', 'USA');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (70, 'MANUFACTURER', 'HONDA');
    INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (70, 'COUNTRY', 'UK')
    ;
    /
    COMMIT;
    Attached Thumbnails Attached Thumbnails SQL_Input_Output.JPG  

  2. #2
    Join Date
    Dec 2013
    Posts
    14
    This query should give you the required result

    select max(id),MANUFACTURER,country from (
    SELECT
    id,
    MAX(DECODE(param_nm, 'MANUFACTURER', PARAM_VAL)) AS MANUFACTURER,
    MAX(DECODE(param_nm, 'COUNTRY', param_val)) AS country
    FROM tb_test_01
    GROUP BY id
    ORDER BY id)
    group by MANUFACTURER,country
    order by max(id)

Posting Permissions

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