[create] table t1
(id int,name varchar(20),address varchar(20),T_name varchar(20),[count] int)insert t1 values (1,'AA','9U','B1',1)
insert t1 values (2,'AA','9U','B1',4)
insert t1 values (3,'AA','9U','B2',3)
insert t1 values (4,'AA','9U','B2',4)
insert t1 values (5,'AA','5Y','B1',4)
insert t1 values (6,'AA','5Y','C1',2)
insert t1 values (7,'AA','W3','C1',6)
insert t1 values (8,'AA','DD','D2',8)
declare @sql varchar(2000)
select @sql='select [name],address'
select @sql=@sql+',['+T_name+']=isnull(sum(case when T_name='''+T_name+''' then
[count] end),0)'
from t1
group by T_name
select @sql=@sql+' from t1 group by [name],address'
exec(@sql)
drop table t1name address B1 B2 C1 D2
-------------------- -------------------- ----------- ----------- ----------- -----------
AA 5Y 4 0 2 0
AA 9U 5 7 0 0
AA DD 0 0 0 8
AA W3 0 0 6 0警告: 聚合或其它 SET 操作消除了空值。
(id int,name varchar(20),address varchar(20),T_name varchar(20),[count] int)insert t1 values (1,'AA','9U','B1',1)
insert t1 values (2,'AA','9U','B1',4)
insert t1 values (3,'AA','9U','B2',3)
insert t1 values (4,'AA','9U','B2',4)
insert t1 values (5,'AA','5Y','B1',4)
insert t1 values (6,'AA','5Y','C1',2)
insert t1 values (7,'AA','W3','C1',6)
insert t1 values (8,'AA','DD','D2',8)
declare @sql varchar(2000)
select @sql='select [name],address'
select @sql=@sql+',['+T_name+']=isnull(sum(case when T_name='''+T_name+''' then
[count] end),0)'
from t1
group by T_name
select @sql=@sql+' from t1 group by [name],address'
exec(@sql)
drop table t1name address B1 B2 C1 D2
-------------------- -------------------- ----------- ----------- ----------- -----------
AA 5Y 4 0 2 0
AA 9U 5 7 0 0
AA DD 0 0 0 8
AA W3 0 0 6 0警告: 聚合或其它 SET 操作消除了空值。
(id int,name varchar(20),address varchar(20),T_name varchar(20),[count] int)insert t1 values (1,'AA','9U','B1',1)
insert t1 values (2,'AA','9U','B1',4)
insert t1 values (3,'AA','9U','B2',3)
insert t1 values (4,'AA','9U','B2',4)
insert t1 values (5,'AA','5Y','B1',4)
insert t1 values (6,'AA','5Y','C1',2)
insert t1 values (7,'AA','W3','C1',6)
insert t1 values (8,'AA','DD','D2',8)
declare @sql varchar(2000)
select @sql='select [name],address'
select @sql=@sql+',['+T_name+']=isnull(sum(case when T_name='''+T_name+''' then
[count] end),0)'
from t1
group by T_name
select @sql=@sql+' from t1 group by [name],address'
select @sql='select [id]=(select count(1) from ('+@sql+') a where a.address<=b.address
and a.name=b.name),*
from ('+@sql+') b '
exec(@sql)drop table t1
id name address B1 B2 C1 D2
----------- -------------------- -------------------- ----------- ----------- ----------- -----------
1 AA 5Y 4 0 2 0
2 AA 9U 5 7 0 0
3 AA DD 0 0 0 8
4 AA W3 0 0 6 0警告: 聚合或其它 SET 操作消除了空值。
(
[id] int ,
[name] varchar(10),
ADDRESS varchar(10),
T_NAME varchar(10),
[count] int,
[DATE] datetime
)
insert table1
select 1,'AA','9U','B1',1,'2005-01-01' union
select 2,'AA','9U','B1',4,'2005-04-01' union
select 3,'AA','9U','B2',3,'2005-01-02' union
select 4,'AA','9U','B2',4,'2005-09-01' union
select 5,'AA','5Y','B1',4,'2005-07-05' union
select 6,'AA','5Y','C1',2,'2005-09-04' union
select 7,'AA','W3','C1',6,'2005-08-09' union
select 7,'AA','DD','D2',8,'2005-07-08'select T2.T_Id,T1.* from
(select distinct M.name,M.AddRess,isnull(M.B1,0) as B1,isnull(M.B2,0) as B2,isnull(M.C1,0) as C1 from (select Tb.NAME,Tb.ADDRESS,
(select T.cou from (select NAME,ADDRESS,T_NAME,sum([count]) as Cou from table1 group by NAME,ADDRESS,T_NAME) T where T.NAME=Tb.NAME and T.ADDRESS=Tb.ADDRESS and T.T_name='B1') as B1,
(select T.cou from (select NAME,ADDRESS,T_NAME,sum([count]) as Cou from table1 group by NAME,ADDRESS,T_NAME) T where T.NAME=Tb.NAME and T.ADDRESS=Tb.ADDRESS and T.T_name='B2') as B2,
(select T.cou from (select NAME,ADDRESS,T_NAME,sum([count]) as Cou from table1 group by NAME,ADDRESS,T_NAME) T where T.NAME=Tb.NAME and T.ADDRESS=Tb.ADDRESS and T.T_name='C1') as C1
from (select NAME,ADDRESS,T_NAME,sum([count]) as Cou from table1 group by NAME,ADDRESS,T_NAME) Tb) M) T1,
(select (select count(*)+1 from (select AddRess,count(*) as Cou from table1 group by AddRess) TL where TL.AddRess<T.AddRess) as T_Id,T.AddRess
from (select AddRess,count(*)as Cou from table1 group by AddRess ) T) T2
where T1.AddRess=T2.AddRess