SQL Server中的动态数据透视表列 [英] Dynamic Pivot Columns in SQL Server
本文介绍了SQL Server中的动态数据透视表列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在SQL Server中有一个名为Property的表,其中包含以下各列:
I have a table named Property with following columns in SQL Server:
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.
推荐答案
类似以下内容:
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小提琴演示.
这会给你这样的东西:
SQL Fiddle Demo.
This will give you something like this:
| OBJECT_ID | PROPERTY1 | PROPERTY2 | PROPERTY3 | PROPERTY4 |
-------------------------------------------------------------
| 1 | ee | fd | fdf | ewre |
| 2 | dsd | sss | dfew | dff |
这篇关于SQL Server中的动态数据透视表列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文