[]SQL 2005 用一条语句能否实现拼接效果

[求助]SQL 2005 用一条语句能否实现拼接效果
数据环境：SQL 2005

测试数据：

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] ([ID] [int] IDENTITY(1,1) not null, strName varchar(20),intSL int)
insert [tb] 
select '小明' , 10 union all
select '小明' , 20 union all
select '小明' , 20 union all
select '小红' , 20 union all
select '小红' , 20 union all
select '小红' , 30 
go
--测试数据
select * from tb

--想用一条汇总语句实现如下图效果（不用左右链接语句进行拼接）。



[解决办法]

SQL code

declare @t table ([ID] [int] IDENTITY(1,1) not null, strName varchar(20),intSL int)insert @t  select '小明' , 10 union allselect '小明' , 20 union allselect '小明' , 20 union allselect '小红' , 20 union allselect '小红' , 20 union allselect '小红' , 30  ;with t1 as (    select min([ID]) as ID,strName,sum(intSL) as intSL    from @t a     group by strName),t2 as (    select strName,sum(intSL) as intSL2 from (        select row_number() over (partition by strName order by id ) as Row,strName,intSL         from @t     ) tmp where Row<=2    group by strName),t3 as (    select strName,sum(intSL) as intSL3 from (        select row_number() over (partition by strName order by id ) as Row,strName,intSL         from @t where intSL > 10    ) tmp where Row<=2    group by strName)select row_number() over (order by a.id) as ID,a.strName,a.intSL,b.intSL2,c.intSL3from t1  a inner join t2 b on a.strName = b.strName inner join t3 c on a.strName = c.strName/*(6 行受影响)ID                   strName              intSL       intSL2      intSL3-------------------- -------------------- ----------- ----------- -----------1                    小明                   50          30          402                    小红                   70          40          40(2 行受影响)*/
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb] ([ID] [int] IDENTITY(1,1) not null, strName varchar(20),intSL int)insert [tb]  select '小明' , 10 union allselect '小明' , 20 union allselect '小明' , 20 union allselect '小红' , 20 union allselect '小红' , 20 union allselect '小红' , 30 union allselect '小东' , 10 goselect   strname,   sum(intSL) as intSL1,   sum(case when px<=2 then intSL else 0 end) as intSL2,   sum(case when px>2 then intSL else 0 end) as intSL3from   (select px=row_number()over(partition by strname order by getdate()),* from tb)tgroup by    strname     /* strname              intSL1      intSL2      intSL3-------------------- ----------- ----------- -----------小东                   10          10          0小红                   70          40          30小明                   50          30          20(3 行受影响)*/