110,534
社区成员
发帖
与我相关
我的任务
分享
DECLARE @sql VARCHAR(8000)
SELECT @Sql=ISNULL(@sql+',','')+'['+delDate+']' FROM tPo2
WHERE vendor = '50002903'
GROUP BY delDate
ORDER BY delDate DESC
SET @sql='SELECT * FROM tPo2 PIVOT (SUM(GRQty2) FOR delDate IN ('+@sql+')) a WHERE vendor = '+ '50002903'
EXEC (@sql)
class Key
{
public string vendor;
public string purchDoc;
public string material;
public string shoText;
public override int GetHashCode()
{
unchecked
{
return
((vendor.GetHashCode() * 33 +
purchDoc.GetHashCode()) * 33 +
material.GetHashCode()) * 33 +
shoText.GetHashCode();
}
}
public override bool Equals(object obj)
{
Key other = obj as Key;
return other != null &&
other.vendor == this.vendor &&
other.purchDoc == this.purchDoc &&
other.material == this.material &&
other.shoText == this.shoText;
}
}
class Value
{
public Dictionary<string, string> data;
}
Dictionary<Key, Value> table; //hashtable
转换过程
DataTable dt; //要操作的数据表
//合并过程
foreach (DataRow row in dt.Rows)
{
Key key = new Key
{
vendor = (string)row["vendor"],
purchDoc = (string)row["purchDoc"],
material = (string)row["material"],
shoText = (string)row["shoText"],
};
Value value;
//查找
if(!table.TryGetValue(key, out value))
{
value = new Value();
table.Add(key, value);
}
//合并value, 5是日期开始的列号
for (int col = 5; col < dt.Columns.Count; ++col)
{
if (!value.data.ContainsKey(dt.Columns[col].ColumnName) && row[col] != null)
value.data.Add(dt.Columns[col].ColumnName, (string)row[col]);
}
}
//写入合并后的数据
//这边可以直接写sql
foreach (var item in table)
{
DataRow newrow = dt.NewRow();
newrow["vender"] = item.Key.vendor;
//...........
foreach (var item2 in item.Value.data)
{
newrow[item2.Key] = item2.Value;
}
dt.Rows.Add(newrow);
}