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.
- 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.
- Now we will use dynamic sql to create our new table that will support the columns needed in the horizontal table.
- 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)
|Jack Wilsonfirstname.lastname@example.org||2009 Review||John Sterrett|
|Jack Wilsonemail@example.com||2009 Review||Bo Smith|
|Hank Reedfirstname.lastname@example.org||2008 Review||John Sterrett|
|Jack Wilsonemail@example.com||2008 Review||Chris Cupp|
Figure 1 – The vertical table
The following is the horizontal table (output)
|Jack Wilsonfirstname.lastname@example.org||2009 Review||John Sterrett||2009 Review||Bo Smith||2008 Review||John Sterrett|
|Hank Reedemail@example.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.