One of the key tasks of a Business Intelligence team is to provide business users with insights into the data generated and stored by business systems. In the insurance industry, for example, understanding the number of claims received versus successfully processed claims can provide valuable information about business performance.

However, the data may not always be stored in a format that is easy to analyze. In some cases, the data may be spread across multiple rows, making it difficult to gain a comprehensive understanding of the information. This is where transposing rows into columns can be useful.

Transposing rows into columns involves rearranging the data so that each unique identifier (such as a policy number) is represented in a single row with repeating columns for each data point. This allows for easier analysis and reporting.

In this article, we will explore different options for transposing rows into columns in SQL Server. We will discuss the pros and cons of each option and evaluate their performance.

Option #1: PIVOT

The PIVOT function in SQL Server is one of the simplest methods for transposing rows into columns. It allows you to specify the columns you want to pivot on and the values you want to display in the new columns.

Here is an example of how to use the PIVOT function:

SELECT *
FROM (
    SELECT [PolNumber], [PolType], [Effective Date], [DocName], [Submitted]
    FROM [dbo].[InsuranceClaims]
) AS SourceTable
PIVOT (
    AVG([Submitted])
    FOR [DocName] IN ([Doc A], [Doc B], [Doc C], [Doc D], [Doc E])
) AS PivotTable;

This query will transpose the data from the original table into a new table with repeating columns for each document name. The output will be similar to Table 2 in the example.

While the PIVOT function is simple to use, it has some limitations. It requires a predefined list of possible fields, so if new documents are added, the query will need to be updated. Additionally, the performance of the PIVOT function may not be optimal for large datasets.

Option #2: CURSOR

Although the use of cursors in SQL Server is generally discouraged, there are cases where they can be useful, such as when transposing rows into columns. Cursors allow you to process rows one at a time and perform operations on them.

Here is an example of how to use a cursor to transpose rows into columns:

DECLARE @PolNumber NVARCHAR(255), @PolNumber5 NVARCHAR(255), @PolType VARCHAR(255), @DocName NVARCHAR(255), @Submitted INT, @Eff DATE, @message_T NVARCHAR(MAX);

SET @message_T = '';
SET @PolNumber5 = '';

DECLARE policyDocs_csr CURSOR FOR
SELECT [PolNumber], [PolType], [Effective Date], [DocName], [Submitted]
FROM [dbo].[InsuranceClaims]
ORDER BY [PolNumber];

OPEN policyDocs_csr;

FETCH NEXT FROM policyDocs_csr INTO @PolNumber, @PolType, @Eff, @DocName, @Submitted;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @PolNumber5 <> @PolNumber
        SET @message_T = @message_T + CHAR(13) + @PolNumber + ' | ' + @PolType + ' | ' + CONVERT(VARCHAR, @eff) + ' | ' + @DocName + ' (' + CONVERT(VARCHAR, ISNULL(@submitted, '')) + ') | ';
    ELSE IF @PolNumber5 = @PolNumber
        SET @message_T = @message_T + @DocName + ' (' + CONVERT(VARCHAR, ISNULL(@submitted, '')) + ') | ';

    SET @PolNumber5 = @PolNumber;

    FETCH NEXT FROM policyDocs_csr INTO @PolNumber, @PolType, @Eff, @DocName, @Submitted;
END;

IF @@FETCH_STATUS <> 0
    PRINT @message_T;

CLOSE policyDocs_csr;
DEALLOCATE policyDocs_csr;

This script uses a cursor to iterate through the rows of the original table and build a string with the transposed data. The output will be similar to Table 2 in the example.

While the cursor option allows for dynamic rows and columns without updating the script, it can be resource-intensive and may impact SQL Server performance.

Option #3: XML

The XML option for transposing rows into columns is an optimal version of the PIVOT function. It addresses the limitation of the PIVOT function by using a combination of XML Path, dynamic T-SQL, and built-in T-SQL functions.

Here is an example of how to use the XML option:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

SET @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(c.[DocName])
    FROM [dbo].[InsuranceClaims] c
    FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');

SET @query = 'SELECT [PolNumber], ' + @cols + ' FROM (
    SELECT [PolNumber], [PolType], [submitted] AS [amount], [DocName] AS [category]
    FROM [dbo].[InsuranceClaims]
)x
PIVOT (
    MAX(amount) FOR category IN (' + @cols + ')
) p';

EXECUTE (@query);

This script uses XML functions to dynamically generate the columns for the transposed data. The output will be similar to Table 2 in the example.

The XML option is able to handle dynamic rows and columns without updating the script. It also provides good performance and efficient handling of the data.

Option #4: Dynamic SQL

Another alternative to the XML option is to use purely dynamic SQL to transpose rows into columns. This option utilizes the same built-in T-SQL functions as the XML option.

Here is an example of how to use dynamic SQL:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME([Name])
FROM (
    SELECT [DocName] AS [Name]
    FROM [dbo].[InsuranceClaims] AS p
    GROUP BY [DocName]
) AS x;

SET @sql = N'SELECT [PolNumber], ' + STUFF(@columns, 1, 2, '') + ' FROM (
    SELECT [PolNumber], [Submitted] AS [Quantity], [DocName] as [Name]
    FROM [dbo].[InsuranceClaims]
) AS j
PIVOT (
    SUM(Quantity) FOR [Name] IN (' + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';

EXEC sp_executesql @sql;

This script dynamically generates the columns for the transposed data using dynamic SQL. The output will be similar to Table 2 in the example.

Like the XML option, dynamic SQL can handle dynamic rows and columns without updating the script. It provides good performance and efficient handling of the data.

Conclusion

In this article, we have explored different options for transposing rows into columns in SQL Server. The PIVOT option is simple to use but has limitations in handling dynamic columns. The CURSOR option allows for dynamic rows and columns but can be resource-intensive. The XML and Dynamic SQL options provide optimal solutions for transposing rows into columns, with good performance and efficient handling of dynamic data.

When choosing the right option for your specific scenario, consider the complexity of the data, the performance requirements, and the ease of maintenance. Each option has its own advantages and trade-offs, so choose the one that best suits your needs.