This is because the Pivot function works with only a predefined list of possible fields. Pol003), we are able to automatically retrieve them without making any changes to Script 1.Īlthough, we don’t have to alter the script to show additional policies, we unfortunately have to update it if we need to return more columns. The results of executing Script 1 are shown in Figure 1, as it can be seen, the output is exactly similar to that of Table 2.įurthermore, as we add more policy numbers in our dataset (i.e. Script 1: Transpose data using Pivot function Script 1 shows how a Pivot function can be utilised. Using a T-SQL Pivot function is one of the simplest method for transposing rows into columns. Therefore, the execution plan and I/O statistics of each T-SQL option will be evaluated and analysed using ApexSQL Plan. Some of the T-SQL options that will be demonstrated will use very few lines of code to successfully transpose Table 1 into Table 2 but may not necessary be optimal in terms query execution. The objective of this article is to demonstrate different SQL Server T-SQL options that could be utilised in order to transpose repeating rows of data into a single row with repeating columns as depicted in Table 2. Thus, a correct representation of this data ought to be in a single row that contains a single instance of policy Pol002 as shown in Table 2:

Such data could be stored in source system as per the layout in Table 1:Īlthough each data entry in Table 1 has a unique RecKey identifier, it all still relates to a single policy claim (policy Pol002). A typical understanding of data within an insurance industry could relate to measuring the number of claims received vs successfully processed claims. Understanding the data should give business users an insight into how the business is performing. One of the primary functions of a Business Intelligence team is to enable business users with an understanding of data created and stored by business systems.

Excel rotate row to column