SQL Server 中的动态枢轴列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14797691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic Pivot Columns in SQL Server
提问by Ashkan
I have a table named Property with following columns in SQL Server:
我在 SQL Server 中有一个名为 Property 的表，其中包含以下列：
Id Name
there are some property in this table that certain object in other table should give value to it.
该表中有一些属性，其他表中的某些对象应该为其赋值。
Id Object_Id Property_Id Value
I want to make a pivot table like below that has one column for each property I've declared in 1'st table:
我想制作一个像下面这样的数据透视表，它为我在第一个表中声明的每个属性有一列：
Object_Id Property1 Property2 Property3 ...
I want to know how can I get columns of pivot dynamically from table. Because the rows in 1'st table will change.
我想知道如何从表中动态获取数据透视列。因为第一个表中的行会改变。
回答by Mahmoud Gamal
Something like this:
像这样的东西：
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, '');
SELECT @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 ; ';
execute(@query);
SQL Fiddle Demo.
SQL 小提琴演示。
This will give you something like this:
这会给你这样的东西：
| OBJECT_ID | PROPERTY1 | PROPERTY2 | PROPERTY3 | PROPERTY4 |
-------------------------------------------------------------
| 1 | ee | fd | fdf | ewre |
| 2 | dsd | sss | dfew | dff |
