移调DataTable中的ASP.NET [英] Transpose in Datatable in ASP.NET

问题描述

我的DataTable有两栏为ID，值数据为：

I have DataTable with two Column as "ID", "Value" with data as:

ID    Value
A   100
A   200
A   300
A   400
A   500
B   -100
B   -99
B   -98
B   -97
C   1
C   2
C   3
C   4

我想在GridView控件的显示此：

I want to display this in GridView as:

A   B   C
100-100 1
200 -99 2
300 -98 3
400 -97 4

这是DataTable中以变调最好的方法。这将是非常有益的，如果任何一个可以提供一个例子。

Which is the best way to Transpose this in DataTable. It would be really helpful if any one can provide an example.

问候

推荐答案

我相当熟悉的OSI PI数据和我拉PI标签数据到一个SQL Server数据库后，做了同样的。

I'm fairly familiar with OSI PI data and I've done the same after pulling PI tag data into an SQL Server database.

诀窍是，需要有与开始时间或结束时间另一列因此对于A，B和C正确的行可以匹配。

The trick is that there needs to be another column with the Start Time or End Time so the correct rows for A, B, and C can be matched up.

然后，它只是一个使用PIVOT（仅适用于SQL Server的2005+）将它们分组的事：

Then it's just a matter of using PIVOT (SQL Server 2005+ only) to group them:

SELECT *
FROM (SELECT ts_start, ID, Value FROM DataTable) v
PIVOT( SUM(Value) FOR ID IN ([A],[B],[C]) ) AS pvt

您可以只使用你想要任何集合（MAX，MIN，SUM等）上面，它不只要有只有一个标签和时间戳的每个不同组合的价值问题。聚合是由枢必需的，如同别名 v 和 PVT （您可以命名他们不管你喜欢）。

You can use just about any aggregate you want (MAX, MIN, SUM, etc.) above, it doesn't matter as long as there's only one value for each distinct combination of tag and timestamp. Aggregation is required by PIVOT, as are the aliases v and pvt (you can name them whatever you like).

这篇关于移调DataTable中的ASP.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！