How to Pivot and Concatenate Columns in SQL Server Using the PIVOT Operator?

Pivot Table and Concatenate Columns in SQL Server

The given SQL Server database contains records with ID, TYPE, SUBTYPE, COUNT, and MONTH columns. The goal is to transform this data into a pivoted table where TYPE and SUBTYPE are concatenated into new columns and COUNT is summed for matching ID and MONTH values.

To achieve this, SQL Server provides the PIVOT operator, which dynamically generates columns based on provided values. Here's how you can approach this problem using PIVOT:

DECLARE @sql AS VARCHAR(MAX);
DECLARE @pivot_list AS VARCHAR(MAX);
DECLARE @select_list AS VARCHAR(MAX);

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 TableName
) AS PIVOT_CODES;

SET @sql = ';WITH p AS (
    SELECT ID, [MONTH], [TYPE] + ''_'' + SUBTYPE AS PIVOT_CODE, SUM([COUNT]) AS [COUNT]
    FROM TableName
    GROUP BY ID, [MONTH], [TYPE] + ''_'' + SUBTYPE
)
SELECT ID, [MONTH], ' + @select_list + '
FROM p
PIVOT (
    SUM([COUNT])
    FOR PIVOT_CODE IN (
        ' + @pivot_list + '
    )
) AS pvt';

EXEC (@sql);

This code dynamically generates the required column list and uses the PIVOT operator to create the pivoted table. The resulting output will have the desired format with concatenated TYPE and SUBTYPE columns and summed COUNT values.

The above is the detailed content of How to Pivot and Concatenate Columns in SQL Server Using the PIVOT Operator?. For more information, please follow other related articles on the PHP Chinese website!