How to Dynamically Generate Pivot Columns in SQL Server?

Dynamic pivot columns in SQL Server

In SQL Server, dynamic pivot columns allow you to create a pivot table whose columns change based on values ​​in another table. This is useful when the number of pivot columns is unknown in advance or changes frequently.

Consider the following form:

• Property: Contains a list of properties.
• PropertyObjects: Link properties to objects and provide values ​​for properties.

Problem Statement

You want to create a pivot table that contains one column for each property in the Property table. However, the number of properties may change, so you need a way to get the column names dynamically.

Solution

To create dynamic pivot columns, you can use dynamic SQL with the PIVOT clause. The following code demonstrates one solution:

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

-- 获取作为逗号分隔字符串的唯一属性名称
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Name)
                      FROM property
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 构造动态查询
SET @query =
'SELECT *
FROM
(
  SELECT
    o.object_id,
    p.Name,
    o.value
  FROM PropertyObjects AS o
  INNER JOIN property AS p ON o.Property_Id = p.Id
) AS t
PIVOT 
(
  MAX(value)
  FOR Name IN (' + @cols + ')
) AS p;';

-- 执行动态查询
EXEC(@query);</code>

Results

The dynamic query will return a pivot table containing one column for each property in the Property table. The value in the pivot table will be the maximum value for each attribute-object combination.

For example, if the Property table contains the following properties:

• Property1
• Property2
• Property3

and the PropertyObjects table contains the following data:

OBJECT_ID	PROPERTY_ID	VALUE
1	1	ee
1	2	fd
1	3	fdf
1	4	ewre
2	1	dsd
2	2	sss
2	3	dfew
2	4	dff

Dynamic query will return the following pivot table:

OBJECT_ID	PROPERTY1	PROPERTY2	PROPERTY3	PROPERTY4
1	ee	fd	fdf	ewre
2	dsd	sss	dfew	dff

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