How do I manipulate this table?

[#1]

Hey All,

I have a table for maintenance plans from the database and the table structure is making things difficult.

The fields look like...

LOAD

    PlansID

    "plans_compid" as %CompanyID,

    "plans_Sage90expdate" as [Sage 100 EXP Date],

    "plans_sageprice" as [Sage 100 Price],

    "plans_sagetype" as [Sage 100 Type],

    "plans_swkdate" as [SWK Support EXP Date],

    "plans_swksuptype" as [SWK Support Type],

    "plans_swksupprice" as [SWK Support Price],

    "plans_sage500expdate" as [Sage 500 EXP Date] ,

    "plans_500price" as [Sage 500 Price],

    "plans_bwexpdate" as [BusinessWorks EXP Date],

    "plans_bwtype" as [BusinessWorks Type],

    "plans_Sagebwprice" as [BusinessWorks Price],

    "plans_FASexpdate" as [FAS EXP Date],

    "plans_FASprice",

    "plans_DSDexpdate",

    "plans_DSDplantype",

    "plans_DSDprice"....

FROM

And that goes on for around 40 products. 

Ideally -- i'd like to squash those fields into only a handful.  It would look like:

LOAD

CompanyID

Maintenance Product

Maintenance Type

Exp Date

[jduenyas]

After creating your connection string (Connect in the Script side) select Select which will take you to your database. There select the Fields you wish to import and only those will be loaded.

[#3]

Sorry, Josh – I probably wasn’t clear enough.

I want all of those fields, but I want to transform them into the same four fields so that the granularity is based on company. There will be a lot of nulls, but that is fine with me. It will allow me to get an accurate min/max for my calendar and it will make naming WAY easier.

[rwunderlich]

Take a look at the CrossTable Load. That will transpose your columns into rows.

-Rob