Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Set column name as the first value

  1. #1

    Default Set column name as the first value

    I have this table with 3 columns ('column_1', 'column_2', 'column_3'):

    column_1 column_2 column_3
    name 1 name 2 name 3
    value 1 value 2 value 3
    value 4 value 5 value 6

    I'm tryng to write an ETL to get out a table with 3 columns, which name is 'name_1', 'name_2' and 'name_3' (the name of the value of the first row):

    name 1 name 2 name 3
    value 1 value 2 value 3
    value 4 value 5 value 6

    Anyone could help me?

    Thanks!

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    import the data skipping the first line?
    -- Mick --

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    @Mick: You're not done that way, you need to build the target table first.

    So it's a bit more complicated:

    • Identify the row containing the column names in the source table and use the names to create the target table.
    • Then use all the other rows and insert them into the target table.
    So long, and thanks for all the fish.

  4. #4

    Default

    Yes! The problem is as Marabu stated.

    I know that the first row contain the column names. I don't know how I can use the value to set the column name.

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Isn't SQL DDL your preferred method to create a table, too?
    So pick up the fieldnames, settle for a VARCHAR column type large enough to accept the biggest value, and synthesize the CREATE TABLE statement.
    You can use a couple of standard steps for this (Add-Constants, Normalize-Row, Group-By) or one of the Scripting steps (Java, JavaScript).
    Finally you can use Execute-Row-SQL-Script to apply your CREATE statement.
    So long, and thanks for all the fish.

  6. #6

    Default

    Thank you very much for your help.

    I've used the Row Normalize step.
    Now I have this flow:

    column_names
    name_1
    name_2
    name_3

    Now I don't know how to use the "Execute row SQL script" step. I've never used it before. Could you help me again, please?
    Last edited by uilli; 12-16-2015 at 06:29 PM.

  7. #7

    Default

    Maybe I have understood.
    I need to modify each row in this way:

    "ALTER TABLE mytable CHANGE name_of_column_1 name_1 VARCHAR(255);"
    "ALTER TABLE mytable CHANGE name_of_column_2 name_2 INT(3);"
    ...

    and than use "execute SQL script" step.
    Don't it?

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    So the table already exists and you just want to rename the columns?
    Why would you bother to rename columns?
    Use generic column names and create a view with the right names.
    So long, and thanks for all the fish.

  9. #9

    Default

    Unfortunately I need to rename the column name.
    But the "execute SQL script" step seem to work fine for me.
    Later I will complete my transformation and I could write about it.
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.