How to Dynamically Create a Pivot Table with Concatenated Columns in SQL Server?

Pivot Table and Concatenate Columns in SQL Server

Transforming database tables to display data in a more organized and meaningful way is often necessary for data analysis and presentation. In this specific case, the goal is to create a "pivot table" from a database table, where the TYPE and SUBTYPE columns are concatenated into new columns and the COUNT values are aggregated by the ID and MONTH columns.

Understanding the PIVOT Operator

SQL Server offers the PIVOT operator, which enables you to transform data from a row-oriented format to a column-oriented format. It allows you to group data by one or more columns and create new columns based on the values in another column.

Dynamic Generation of Pivot Columns

In the given scenario, there are potentially "100s" of TYPE and SUBTYPE combinations, making it impractical to hard-code the pivot columns. To overcome this, dynamic SQL can be used to generate the pivot column list based on the unique combinations of TYPE and SUBTYPE values.

Dynamic SQL Implementation

The following SQL code demonstrates how to dynamically generate the pivot columns and execute the PIVOT operation:

DECLARE @sql AS NVARCHAR(MAX);
DECLARE @pivot_list AS NVARCHAR(MAX) = NULL;  -- Leave NULL for COALESCE technique
DECLARE @select_list AS NVARCHAR(MAX) = NULL; -- Leave NULL for COALESCE technique

-- Build the pivot column list and select list
SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + PIVOT_CODE + ']',
       @select_list = COALESCE(@select_list + ', ', '') + 'ISNULL([' + PIVOT_CODE + '], 0) AS [' + PIVOT_CODE + ']'
FROM (
    SELECT DISTINCT TYPE + '_' + SUBTYPE AS PIVOT_CODE
    FROM YourTableName
) AS PIVOT_CODES;

-- Generate the dynamic SQL query
SET @sql = '
WITH P AS (
    SELECT ID, [MONTH], TYPE + ''_'' + SUBTYPE AS PIVOT_CODE, SUM(COUNT) AS [COUNT]
    FROM YourTableName
    GROUP BY ID, [MONTH], TYPE + ''_'' + SUBTYPE
)
SELECT ID, [MONTH], ' + @select_list + '
FROM P
PIVOT (
    SUM([COUNT])
    FOR PIVOT_CODE IN (
        ' + @pivot_list + '
    )
) AS PVT';

-- Execute the dynamic SQL query
EXEC (@sql);

Benefits of Using Dynamic SQL

By using dynamic SQL, the pivot columns are generated automatically, eliminating the need for manual concatenation. This approach is flexible and can be applied to tables with numerous TYPE and SUBTYPE combinations.

Note:

The above code uses the COALESCE function to concatenate the pivot column list and select list. Alternatively, you can manually specify the concatenation operator (',' or ';') as needed.

The above is the detailed content of How to Dynamically Create a Pivot Table with Concatenated Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!