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 jwilson@comp.com 2009 Review John Sterrett
Jack Wilson jwilson@comp.com 2009 Review Bo Smith
Hank Reed hreed@comp.com 2008 Review John Sterrett
Jack Wilson jwilson@comp.com 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 jwilson@comp.com 2009 Review John Sterrett 2009 Review Bo Smith 2008 Review John Sterrett
Hank Reed hreed@comp.com 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.

  • Brandon M. Hunter

    Good read……I’m wondering do you gain any performance when converting a vertical to a horizontal table?

  • Great post, this script will come in very useful… thanks again John

  • Hi Jon,

    I am glad you find the script useful.

    Regards,
    John Sterrett

  • Great script! One of my clients is after something like this for their customer database so thank you very much.

  • hasan

    i agree

  • ucha

    error like below when try to execute
    Server ‘SMUASEDEV’, Line 69:
    Must declare variable ‘@InsertString’.
    Server Message: Number 137, Severity 15
    Server ‘SMUASEDEV’, Line 70:
    Must declare variable ‘@InsertString’.
    Server Message: Number 102, Severity 15
    Server ‘SMUASEDEV’, Line 75:
    Incorrect syntax near ‘PersonCursor’.

  • Dustin

    Hi John, Great script! I want to add another column in the way employee and review works. So i’m looking for a way to add a third dynamic column in the horizontal table. Can you help me out with this?