Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Creating New Table with Selected Column Names

  1. #1
    Join Date
    Mar 2016
    Posts
    18

    Default Creating New Table with Selected Column Names

    Hello,

    I got a Table Input step with:

    SELECT COLUMN_NAME FROM all_tab_columns WHERE OWNER = 'THOMAS' AND TABLE_NAME = single_row.table_name;

    Now i want to create a new table with the columns selected above. I planned on doing this with a Execute SQL Script step but in order to do that I need the column names as a String and separated by a "," .

    Does someone have another idea or know how i could create such a stream line with all the column names separated by a "," ?

    Thanks.

  2. #2
    Join Date
    Sep 2015
    Posts
    15

    Default

    If you're using MySQL, you can use GROUP_CONCAT
    e.g. SELECT GROUP_CONCAT(COLUMN_NAME) as column_list FROM all_tab_columns WHERE OWNER = 'THOMAS' AND TABLE_NAME = single_row.table_name;

  3. #3
    Join Date
    Mar 2016
    Posts
    18

    Default

    Thank you,
    but I am using SQL Server :/

  4. #4
    Join Date
    Sep 2015
    Posts
    15

    Default

    I've done it in the past with SQL Server as well. It involves using AS XML clause, but I don't remember the specifics. If I can locate an old code sample, I'll post it.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    That might solve the immediate issue, but won't solve the overall use-case.

    When you have the data in one field separated by ,s, you won't be able to reference it the way you want to in the SQL Script step, since it (from what I recall) uses prepared statements, and
    Code:
    CREATE TABLE myTable {
    column1 integer,
    column2 integer }
    and
    Code:
    CREATE TABLE myTable {
    "column1 integer,
    column2 integer" }
    are *VERY* different things.

    Go back to the overall design level, and figure out if (a) you really need to create these tables dynamically, and (b) if your approach to it is the best way to do so.

  6. #6
    Join Date
    Sep 2015
    Posts
    15

    Default

    If I have substituted your table and column identifiers correctly, it should look something like this:
    select
    distinct
    stuff((
    select ',' + atc.COLUMN_NAME
    from all_tab_columns atc
    where atc.COLUMN_NAME = COLUMN_NAME
    and atc.OWNER = 'THOMAS'
    and atc.TABLE_NAME = single_row.table_name
    for xml path('')
    ),1,1,'') as columnlist
    from all_tab_columns
    group by COLUMN_NAME

    You can play around with the inner select, to produce something other than a comma separated list.

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Or you could simply get PDI to do it for you with a "Group By" step and the "Concatenate separated by ," option.

    That still doesn't get around the issue that the overall workflow seems broken.

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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

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