Converting a vertical table to horizontal table

Last week I received a request to convert a vertical table from a vendor application into a horizontal table.  There was one catch, the vertical table included text columns that needed to be pivoted horizontally.  The following was my plan for tackling this request.

  1. You must figure out how many columns are required for the worst case scenario in you horizontal table.  You can do this by multiplying the columns being pivoted by the rows in the vertical table. In this example you know the worst case is eight (see figure two.)  In this example we will assume that you will not know how many columns are needed.
  2. Now we will use dynamic sql to create our new table that will support the columns needed in the horizontal table.
  3. Next we will create a cursor that will loop through the vertical table to create and execute insert statements to populate the horizontal table.

Example of vertical table (Input)

ManagerName ManagerEmail Review Employee
Jack Wilson 2009 Review John Sterrett
Jack Wilson 2009 Review Bo Smith
Hank Reed 2008 Review John Sterrett
Jack Wilson 2008 Review Chris Cupp

Figure 1 – The vertical table

The following is the horizontal table (output)

ManagerName ManagerEmail Review1 Employee1 Review2 Employee2 Review3 Employee3
Jack Wilson 2009 Review John Sterrett 2009 Review Bo Smith 2008 Review John Sterrett
Hank Reed 2008 Review Chris Cupp NULL NULL NULL NULL

Figure 2 – The horizontal table

For this example we used two scripts.  The first script will create a vertical table and insert the sample data.  The second script populates the horizontal table and also prints out all scripts created dynamically to the message window.

If you have any questions please feel free to leave a comment . I will try to point you in the right direction.

7 responses to “Converting a vertical table to horizontal table

Leave a Reply

Your email address will not be published.

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.

This site uses Akismet to reduce spam. Learn how your comment data is processed.