Post reply

Pivoting 1 column table

  • November 7, 2009 at 12:34 pm

    #94033

    Hello all. First off, im not sure if this is the forum i should be posting this on. If it's not im sorry and please direct me to the correct one.

    The scenario im in right now got me puzzled as to what to do (and boy, i like being puzzled).

    Ok, so i have 5 .csv files to import, all separated by TAB (\t) with different amount of columns.

    The application we have in the company import the files into a single table with the amount of columns i specify in the application, but the problem is: i need to specify the maximum amount of columns(22) but instead of inserting nulls for the columns missing on the files that have lesser columns the app just keeps inserting and filling those columns, which completly ruins queries i need to run later on.

    Now, i can tell the application to import everything into a single varchar column.

    what i did then was to add a new column to the table to know which file is which worked well if a cursor.

    i then added a CHAR(9)(tab) before and after each line so i could separate every field with a tally table

    so now i got a 1 column table with everything i need separated by file type, but i need to pivot it back into actual columns so i can create 5 temp tables to work with.

    im not sure this is the correct approach, perhaps something simpler can be done but at this moment i cant think of any and would love some ideas

    Thiago Dantas

    --
    Thiago Dantas
    @DantHimself

  • November 7, 2009 at 8:30 pm

    #1076402

    Double post... no discussion here, please. Keep it all together at the following URL... thanks.

    http://qa.sqlservercentral.com/Forums/Topic815495-338-1.aspx

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply