• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Converting Vertical DB Table to Horizontal Table

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Converting Vertical DB Table to Horizontal Table

    I have a database which I am migrating to a more useable structure, and one of the problems I have is that it was designed to be totally generic - performance and maintainability is appalling.

    The structure of one table is totally vertical:

    InstanceID int, Fieldname VarChar (255), FieldValue VarChar (255)

    InstanceID is non-unique, The FieldValue can be anything, from a name to an email address.

    For each InstanceID I need to extract the list of fields, and their values, and copy them into a new table structure (which I have already created programatically). The list of fields is different for each InstanceID...


    Any suggestions...?
    Vieze Oude Man

    #2
    What's the new structure? And is this Oracle?

    Comment


      #3
      SQL Server 2005...

      The architecture for want of a better description is one where 'Virtual Tables' are defined in the database and some in-house generic sql generator (the calls to which are hard coded which totally defeats the object!) - I have extracted this meta data and created physical, horizontal tables...

      Basically it's now a case of migrating the data into these new tables - I am thinking of creating Views on the vertical table and attempting to extract the data that way, but its the 'rotation' of a vertical table to a horizontal table that is a problem...
      Vieze Oude Man

      Comment


        #4
        How about an example of the old table's data and same for new.

        Comment


          #5
          Ahhh, the Inner Platform Effect.

          I've seen these a few times, and they were always broken beyond repair. I have pity on you...
          Listen to my last album on Spotify

          Comment


            #6
            i have some code

            Comment


              #7
              Originally posted by scotspine View Post
              i have some code
              ... and plenty cheapness.

              Comment


                #8
                public DataTable TurnTable(DataTable source)
                {
                DataTable dest = new DataTable("Turned" + source.TableName);

                dest.Columns.Add(" ");

                foreach (DataRow r in source.Rows)
                dest.Columns.Add(r[0].ToString()); // assign each row the plan name (r[0])


                for (int i = 0; i < source.Columns.Count - 1; i++)
                {
                dest.Rows.Add(dest.NewRow());
                }

                for (int r = 0; r < dest.Rows.Count; r++)
                {
                for (int c = 0; c < dest.Columns.Count; c++)
                {
                if (c == 0)
                {
                dest.Rows[r][0] = source.Columns[r + 1].ColumnName;
                } // the plan name
                else
                {
                dest.Rows[r][c] = source.Rows[c - 1][r + 1];
                }
                }
                }
                dest.AcceptChanges();
                return dest;
                }

                Comment


                  #9
                  Originally posted by scotspine View Post
                  public DataTable TurnTable(DataTable source)
                  It's times like this that:

                  a. I remember that the CUK Admins do real work - sometimes.

                  b. I remember that MS-SQL is utter crap.

                  Comment


                    #10
                    Originally posted by scotspine View Post
                    public DataTable TurnTable(DataTable source)
                    Hang on - a useful post, that didn't ridicule the OP, and might solve a problem??

                    What's that doing here???
                    Best Forum Advisor 2014
                    Work in the public sector? You can read my FAQ here
                    Click here to get 15% off your first year's IPSE membership

                    Comment

                    Working...
                    X