The screenshot is from ApexSQL Plan, a tool to view and analyze SQL Server query execution plans The actual estimated plan depicted in Figure 4, indicates that only a single scan was made against the base table with a majority of the cost (at 77.6%) used for sorting data. Thus, although transposing rows using Pivot operator may seem simple, it may later be difficult to maintain. However, imagine if business later decides to add 100 more documents that are required to process a claim? It would mean that you need to update your Pivot script and manually add those 100 fields. Thus, in order to return column, we would firstly need to update the FOR clause in Script 1 to include and only then would the output reflect as shown in Figure 3. 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.
0 kommentar(er)
