How to Pivot a Table and Concatenate Columns in SQL Server?

Pivot Table and Column Concatenation

Question:

How can I transform a database table with the following structure:

ID    TYPE   SUBTYPE    COUNT   MONTH
 1      A      Z          1       7/1/2008
 1      A      Z          3       7/1/2008
 2      B      C          2       7/2/2008
 1      A      Z          3       7/2/2008

into a table with the following structure:

ID    A_Z   B_C   MONTH
1     4     0     7/1/2008
2     0     2     7/2/2008
1     0     3     7/2/2008

where TYPE and SUBTYPE are concatenated to form new columns (e.g., "A_Z"), and COUNT is summed for matching ID and MONTH values?

Answer:

SQL Server 2005 provides a PIVOT operator that enables the transformation described in the question. Here is a detailed explanation:

1. Generate Pivot Code: Determine the distinct combinations of TYPE and SUBTYPE values. For instance, ['A_Z'].
2. Construct Dynamic SQL: Use dynamic SQL to generate a query that includes the PIVOT operator.

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

3. Execute Dynamic SQL: Execute the generated SQL query to obtain the desired output.

This solution leverages dynamic SQL to construct the PIVOT query based on the unique combinations of TYPE and SUBTYPE values, providing a maintenance-free approach for handling multiple types and subtypes in the database.

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