急急,100分求解

dreamwaters 2006-02-20 01:33:42
有一个表 table1
id name sid
1 a 200
1 b 652
1 c 321
2 d 241
2 e 343
2 g 898

......

1000 x 1136
1000 v 8877
1000 z 1233



我想得到

id name1 name2 name3 sid1 sid2 sid3
1 a b c 200 625 321
2 d e g 241  343 898
......
1000  x v z 1136 8877 1233

怎样实现啊?谢谢各位了
...全文
65 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
-狙击手- 2006-02-20
create table tb (id int,name char(1), sid int)
insert tb
select 1,'a',200 union all
select 1,'b',652 union all
select 1,'c',321 union all
select 2,'d',241 union all
select 2,'e',343 union all
select 2,'g',898 union all
select 3,'h',241 union all
select 3,'i',343 union all
select 3,'j',898

go

select idrow=identity(int,1,1),* into # from tb
select a.id,a.name,a.sid,
b.name,b.sid,c.name,c.sid
from
(select id,name,sid from # where idrow%3 = 1) a,
(select id,name,sid from # where idrow%3 = 2) b,
(select id,name,sid from # where idrow%3 = 0) c
where a.id = b.id and a.id = c.id
drop table #,tb/*


id name sid name sid name sid
----------- ---- ----------- ---- ----------- ---- -----------
1 a 200 b 652 c 321
2 d 241 e 343 g 898
3 h 241 i 343 j 898

*/
回复
子陌红尘 2006-02-20
select identity(int,1,1) as rid,1 as nid,* into #t from table1
update a set nid=(select count(*) from #t where id=a.id and rid<=a.rid) from #t a

select
id,
name1=max(case nid when 1 then name end),
name2=max(case nid when 2 then name end),
name3=max(case nid when 3 then name end),
sid1 =max(case nid when 1 then sid end) ,
sid2 =max(case nid when 2 then sid end) ,
sid3 =max(case nid when 3 then sid end)
from
#t
group by id order by id
回复
zjcxc 2006-02-20
-- 下面是sql 2005中的处理方法

-- 示例数据
create table table1(id int, name varchar(10), sid int)
insert table1 select 1,'a',200
union all select 1,'b',652
union all select 1,'c',321
union all select 2,'d',241
union all select 2,'e',343
union all select 2,'g',898
union all select 1000,'x',136
union all select 1000,'v',8877
union all select 1000,'z',1233
go

-- 查询
select id,
name1=max(case rid when 1 then name else '' end),
name2=max(case rid when 2 then name else '' end),
name3=max(case rid when 3 then name else '' end),
sid1=sum(case rid when 1 then sid else '' end),
sid2=sum(case rid when 2 then sid else '' end),
sid3=sum(case rid when 3 then sid else '' end)
from(
select *, rid=row_number() over(partition by id order by name)
from table1 a
)aa
group by id
go

drop table table1

/*--结果
id name1 name2 name3 sid1 sid2 sid3
----------- ---------- ---------- ---------- ----------- ----------- -----------
1 a b c 200 652 321
2 d e g 241 343 898
1000 v x z 8877 136 1233

(3 row(s) affected)

--*/
回复
zjcxc 2006-02-20
-- 示例数据
create table table1(id int, name varchar(10), sid int)
insert table1 select 1,'a',200
union all select 1,'b',652
union all select 1,'c',321
union all select 2,'d',241
union all select 2,'e',343
union all select 2,'g',898
union all select 1000,'x',136
union all select 1000,'v',8877
union all select 1000,'z',1233
go

-- 查询
select id,
name1=max(case rid when 1 then name else '' end),
name2=max(case rid when 2 then name else '' end),
name3=max(case rid when 3 then name else '' end),
sid1=sum(case rid when 1 then sid else '' end),
sid2=sum(case rid when 2 then sid else '' end),
sid3=sum(case rid when 3 then sid else '' end)
from(
select *, rid=(select count(distinct name) from table1 where id=a.id and name<=a.name)
from table1 a
)aa
group by id
go

drop table table1

/*--结果
id name1 name2 name3 sid1 sid2 sid3
----------- ---------- ---------- ---------- ----------- ----------- -----------
1 a b c 200 652 321
2 d e g 241 343 898
1000 v x z 8877 136 1233

(3 row(s) affected)

--*/
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-02-20 01:33
社区公告
暂无公告