locked
Append numerous columns from one table to another table RRS feed

  • Question

  • Not for sure, but I think that T-SQL can do something like this easily.  I'm normalizing a very large database from an Excel spreadsheet.  There are anywhere from 1-25 codes per each person.  There is a many to many relationship between the E codes and a employee number.  To normalize the data what I'm going to do (based on the fact that I think this is expeditious, if it's not, please tell me!) is the junction table between the glossary of E codes and the employee number.  Below is an example of the junction table.

    emp#     e-code

    1            45.3

    1            e44.2

    1           452

    2            v54

    2           12.2

    So I have the left-hand side of the table already done (I hope).  The 'E-code' side of the table is where I'm stuck.  I have in Excel transposed all of the codes so that they are listed vertically.  So column 1 would have 45.3, e44.2, and 452 in it.  Column 2 would have V54 and 12.2 in it.  Order is imperative here.  I need to append  each new column onto the end of the one that preceded it.  So column two's first code would come right after column 1's last.  And column 3 would be appended to the end of column 2.  Column 4 appended to the end of column 3.  Any clues on if a SQL solution would be best here?

    Tuesday, March 13, 2012 6:05 PM

Answers

  • Just tossed something together and you can use an unpivot in SSIS with your excel source and sql server destination.

    Set up the Excel source as first row has column names

    Attach an unpivot block to that and select all of the available "columns"  you will see one column per empid column in your excel sheet

    In each row put Ecode in the destination column

    Change pivotkey column name to Empid

    Hook this block up to a sql destination



    • Edited by Chuck Pedretti Tuesday, March 13, 2012 7:56 PM
    • Proposed as answer by Naomi N Tuesday, March 20, 2012 3:34 AM
    • Marked as answer by KJian_ Tuesday, March 20, 2012 5:36 AM
    Tuesday, March 13, 2012 7:52 PM

All replies

  •  Order is imperative here.  I need to append  each new column onto the end of the one that preceded it.  So column two's first code would come right after column 1's last.  And column 3 would be appended to the end of column 2.  Column 4 appended to the end of column 3.  Any clues on if a SQL solution would be best here?

    If you plan to create a separate table and join it to the other table to get the results, you might lose the order of the e-Codes unless you have a column which will be used for sequencing.

    What is your source of data, SQL Server?? What is the table structure??


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Tuesday, March 13, 2012 6:17 PM
  • The source of the data is an Excel file that I want to import into SQL Server.  And I suppose your right, so I could get an autonumber generated on every new row.  You can only have 256 columns in a table, right?  I've got around 37,000 records I need to fix o_0.
    Tuesday, March 13, 2012 6:34 PM
  • Something like this?

    CREATE TABLE #Junction
    (
    Id INT,
    Names VARCHAR(100)
    )
    GO
    -- Load sample data
    insert into #Junction values (1, '45.3')
    insert into #Junction values (1, 'e44.2')
    insert into #Junction values (1, '452')
    insert into #Junction values (2, 'v54')
    insert into #Junction values (2, '12.2')

    SELECT J1.Id
               ,AllNames = REPLACE (( SELECT J2.Names AS 'data()'
    FROM #Junction as J2
    WHERE J1.Id = J2.Id
    FOR XML PATH ( '' ) ), ' ', ', ')
    FROM #Junction as J1
    GROUP BY Id

    Tuesday, March 13, 2012 6:51 PM
  • I don't know, I'll have to try 'er out :).  I'm pretty new to SQL Server (if you can't tell by the name).  Could you explain what some of non-basic syntax is (outside of create, insert, etc).

    Thanks! 

    Tuesday, March 13, 2012 6:58 PM
  • It is using FOR XML PATH as a means of concat(ing) rows that are grouped by the Id field.  Using PATH with an empty input string keeps it from generating the element tags in the output.

    Tuesday, March 13, 2012 7:05 PM
  • OK, so say I import this data into SQL Server with the table name "Junction".  And say that the data look like this 

    http://imgur.com/m8oFl Where the first column will be a, second b, etc.  Do I just copy that query and run it?

    thanks again for the help.

    Tuesday, March 13, 2012 7:09 PM
  • I don't quite understand what you are asking - your picture does not appear to correlate empids with ecodes.  It appears to be a matrix of ecodes?

    Tuesday, March 13, 2012 7:13 PM
  • Sorry about that.  The first column corresponds to an employee number like 776533125 something like that.  Every code in the first column is under that number.  http://imgur.com/A8E9N here is the full table.  So you can see each column corresponds to a single person.  But each person can have any number of codes.

    edit: If there's no way to do this without having to manually type in each code maybe SQL isn't the way to go to solve this problem.  there will be literally millions of code.s

    Also, the employee number part of the junction table I have a small script of a program to do that.  So now since I have all of the correct employee numbers (in their correct order) I want to be able to line up all the codes for a given person in that same order. 766006604 will be on 20 rows in the junction table with those e-codes will 'use up' one employee number each.
    Tuesday, March 13, 2012 7:18 PM
  • I'll have to think about that for a bit.  I'm thinking that the data would need to be unpivoted and loaded via ssis or perhaps excel has a way to do it.
    Tuesday, March 13, 2012 7:32 PM
  • I tried to find an Excel solution, but to no avail.  Granted, that doesn't mean that there isn't one.  I'm sure that there's a VB solution out there, but I don't know about it.  I work for a university and I've got access to any software I might need that could do it, I just don't know how to go about it! :)
    Tuesday, March 13, 2012 7:36 PM
  • Just tossed something together and you can use an unpivot in SSIS with your excel source and sql server destination.

    Set up the Excel source as first row has column names

    Attach an unpivot block to that and select all of the available "columns"  you will see one column per empid column in your excel sheet

    In each row put Ecode in the destination column

    Change pivotkey column name to Empid

    Hook this block up to a sql destination



    • Edited by Chuck Pedretti Tuesday, March 13, 2012 7:56 PM
    • Proposed as answer by Naomi N Tuesday, March 20, 2012 3:34 AM
    • Marked as answer by KJian_ Tuesday, March 20, 2012 5:36 AM
    Tuesday, March 13, 2012 7:52 PM
  • One last thought - you would want to add an identity to the target table and make it the primary key.  Then you would want to make sure that this is preserving the order of your data.  It appears that the order will be preserved due to the way that SSIS processes the sheet but you would want to do a bunch of testing if the order is significant.
    Tuesday, March 13, 2012 8:01 PM