如何在列中分组行值并将行转换为列?
问题描述:
我的需求如下:在sql 2005中使用sql查询,如果可能的话要避免存储过程。如何在列中分组行值并将行转换为列?
EmpType EmpName Role
1 Ram Admin
2 Raja Admin
1 John update
2 Tom Admin
1 Adam Admin
我需要的empType A组输出是这样的:
EmpType EmpName Admin Update
1 Ram, John, Adam Adam,Ram John
2 Raja , Tom Tom,Raja
谁能帮我。
感谢,
答
要执行你需要编写concatinates字符串自己的聚合功能的任务。这里有一个例子:
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=8000)]
public struct strconcat : IBinarySerialize{
private List values;
public void Init() {
this.values = new List();
}
public void Accumulate(SqlString value) {
this.values.Add(value.Value);
}
public void Merge(strconcat value) {
this.values.AddRange(value.values.ToArray());
}
public SqlString Terminate() {
return new SqlString(string.Join(", ", this.values.ToArray()));
}
public void Read(BinaryReader r) {
int itemCount = r.ReadInt32();
this.values = new List(itemCount);
for (int i = 0; i <= itemCount - 1; i++) {
this.values.Add(r.ReadString());
}
}
public void Write(BinaryWriter w) {
w.Write(this.values.Count);
foreach (string s in this.values) {
w.Write(s);
}
}
}
查询将是:
SELECT * FROM
(Select EmpType, EmpName, Role
FROM TableName) t
PIVOT
(
dbo.strconcat(EmpName) FOR Role IN ([Admin], [update])
) p
您可能还需要检查this link
答
如果你不想创建额外的结构(存储过程,函数等等)这就是你要找的东西:
select
EmpType,
stuff((select ', ' + EmpName from tmp t2 where t2.EmpType = t1.EmpType
for xml path('')),1,2,'') EmpName,
stuff((select ', ' + EmpName from tmp t2 where t2.EmpType = t1.EmpType and t2.Role = 'Admin'
for xml path('')),1,2,'') [Admin],
stuff((select ', ' + EmpName from tmp t2 where t2.EmpType = t1.EmpType and t2.Role = 'Update'
for xml path('')),1,2,'') [Update]
from tmp t1
group by EmpType
这对你有帮助吗?让我们知道。 – 2011-04-11 16:35:11