Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Table Structure For Excel Spreadsheet

    I am needing to import an Excel Spreadsheet into SQL Server 2008 R2 that has headers on both the left and the top in Excel. So sample data would look like this
    Code:
          T1    T2      T3     T4     T5
    1    100   200    300    400    500
    2    300   400    600    900    1000
    3    600   900    1000  1200   1400
    4    900   1200   1500  1800   2000
    And a quick explanation is this is an Excel spreadsheet that has level going down the left side and Tier going across the top and the XP bonus amount is in the center and a Vlookup() and an HLookup() is used to find the amount of XP to apply. So an example would be if we have a Level 3 Tier 2 the bonus XP would be 900. This works in Excel, but what would be the best way to get the same level of detail in SQL Server?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What describes the "best" arrangement for you? Without understanding what you are doing now and what you might want to do in the future, it isn't possible to answer your question.

    Based on the time of year and the way that you've described the problem so far, my first question has to be: "What have you covered in class so far?" because this sounds like an early assignment in an introductory database class.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    What describes the "best" arrangement for you? Without understanding what you are doing now and what you might want to do in the future, it isn't possible to answer your question.

    Based on the time of year and the way that you've described the problem so far, my first question has to be: "What have you covered in class so far?" because this sounds like an early assignment in an introductory database class.

    -PatP
    I am needing to do a lookup. You would first lookup the level from the left side, then lookup the tier from the top and where the two meet is the XP that would be awarded.

    I have not attempted anything as I am not sure if the level and XP should go in one table and have the Tiers listed out in a seperate table. I know doing this would allow me to use the PIVOT() SQL Function to reproduce my exact structure, but that is not what I am after. I am wanting to know how the table in SQL should be structured to allow lookup of this sort.

    Take a stored procedure for example.

    Code:
    Create Procedure [dbo].[GetXPBonux] @Tier int, @Level int
    As
    .....
    And if these values were passed in exec [dbo].[GetXPBonus] 3,2 you would get a return value of 600

    Does that help clarify what I am after?

    (And as a side note this is not a homework assignment, I have been tasked with converting 4 Excel spreadsheets with formulas into SQL Server tables/queries and am stuck on how to set up this one in particular)

Posting Permissions

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