Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16

    Unanswered: Make a horizontal table into a vertical table

    Hi All,
    Any assistance would be greatly appreciated.

    I have a current table which I create on a regular basis from a text file with a layout similar to this:
    Type Policy # Amount Rider 1 Amt Rider 2 Amt
    B 1112H 24.34 12 12.34

    This text file is brought into a staging table with each field (even the amount field) as a varchar (12). I then assign types in a later step in my DTS package.

    What I need to do is stack the riders under each policy so for each policy where there is a rider, there is a new row for every rider.
    So in the example I've given, there would be 2 additional rows for the original first row since there are two riders.
    Type Policy # Amount
    B 1112H 24.34
    R1 1112H 12
    R2 1112H 12.34

    I plan on doing this by first creating a table with just the Type, Policy #, and Amt fields, and then using a series of insert queries where I take the rider (if there is one) and append it onto the table.

    However, I'm getting the following error message when I try:
    Server: Msg 213, Level 16, State 4, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.

    Basically, it wouldn't let me put an 'R1' in the Type column.
    How can I get this to work!?!?

    Thanks in advance for your help

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If the number of rider columns is constant, you can use a Union query like this:
    Insert into DESTINATIONTABLE (Type, [Policy #], Amount)
    Select [Type], [Policy #], [Amount Rider 1] as Amount from SOURCETABLE where [Amount Rider 1] is not null
    UNION
    Select [Type], [Policy #], [Amount Rider 2] as Amount from SOURCETABLE where [Amount Rider 2] is not null
    UNION
    .
    .
    .
    etc
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16
    I can't get past the first INSERT statement for the first rider.
    I'm still getting this error message:

    Server: Msg 213, Level 16, State 5, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.

    I have the same number of columns, the data types shouldn't be an issue. I'm at a stand still!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's probably something simple, like a missing comma that is causing the compiler to miscount the columns.

    And the datatypes may be an issue. Double check them.

    If you want more help, post the layout of your tables (DDL code is best), and the SQL you are trying to run.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16
    Wow blindman, you are a genius, and I'm a big retarded idiot. Yup, stoopid mistake. Importing literally a hundred columns, and one of them was missing a comma. Had to search through the bowels of my dts package, but finally found it. Thanks very much for your help.

    Oh, and I didn't need to use the UNION in mine. Just INSERT INTO worked. Once I located that rogue comma, of course.
    Last edited by melatic; 11-22-04 at 16:37. Reason: ETA stuff about union

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Genius? How do you think I knew what your problem was?

    "An expert is a man who has made all the mistakes which can be made in a very narrow field."
    -Niels Bohr
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16
    Whatever you say... I still think you have at least above average intelligence.

Posting Permissions

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