How to Pivot Tabular Data in SQL by Concatenating Columns and Summing Values?

Converting Tabular Data to Pivot Table Using SQL: Concatenating Columns

In database management, pivoting tables involves transforming data from a column-oriented format to a row-oriented format, effectively "flipping" the table. This article discusses a scenario where you want to pivot a table with multiple columns into a single column with concatenated values from the original columns.

Suppose you have a dataset with columns for ID, TYPE, SUBTYPE, COUNT, and MONTH, arranged as follows:

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

The objective is to convert this data into a pivoted table, where the TYPE and SUBTYPE columns are concatenated into a new column, and the COUNT values are summed for each unique combination of ID and MONTH. The expected output is:

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

SQL Server PIVOT Solution

SQL Server 2005 offers powerful PIVOT and UNPIVOT operators that simplify data pivoting. The following code example demonstrates the PIVOT technique:

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 stackoverflow_159456
) AS PIVOT_CODES

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
'

EXEC (@sql)

The dynamic SQL technique used in this code automatically generates the PIVOT statement based on the distinct combination of TYPE and SUBTYPE values, avoiding hard-coding and making the code maintenance-free.

The above is the detailed content of How to Pivot Tabular Data in SQL by Concatenating Columns and Summing Values?. For more information, please follow other related articles on the PHP Chinese website!