求SQL语句，有没有周末上班的高手，给帮个忙吧！

[zl194]

版本:sql2000 SP4
OS:win2003

问题：我有一个如图所示的表


需求：我想得到下面的表格：并且不想用临时表，最好是视图，可以有中间视图。

cpici cvalue1,cvalue2,cvalue3,cvalue4
-----------------
T501|31|33|5 |  
T502|22|3 |  |
T503|23|44|50|53

数据：

CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null 
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)

INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)

INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)

SELECT * FROM tb1

[华夏小卒]

if object_id('tb1')is not null drop table tb1
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null 
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)

INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)

INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)

----------------------------------

 

if object_id('vv')is not null drop view vv
go
CREATE view vv
as
  
	select cpici,
		cvalue1=max( case when px=1 then cvalue else 0 end),
		cvalue2=max( case when px=2 then cvalue else 0 end),
		cvalue3=max( case when px=3 then cvalue else 0 end),
		cvalue4=max( case when px=4 then cvalue else 0 end)
	from (
		select *,px=(select count(distinct cvalue) from tb1 where cpici=t.cpici and cvalue<=t.cvalue) from tb1 t
	)b
	group by cpici
 
go 


select * from vv

cpici      cvalue1     cvalue2     cvalue3     cvalue4
---------- ----------- ----------- ----------- -----------
T501       5           31          33          0
T502       3           22          0           0
T503       23          44          50          53

(3 行受影响)

[永生天地]

if object_id('tb1') is not null drop table tb1
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null 
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)

INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)

INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)


--在sqlserver2000里需要用自增辅助
alter table tb1 add id int identity
go
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id) from tb1 t)a)t
set @s=@s+' from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id),* from tb1 t
) t group by cpici'

exec(@s)
go
alter table tb1 drop column id 

--再2005就可以用row_number
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by cpici order by getdate()) from tb1)a)t
set @s=@s+' from (select rn=row_number()over(partition by cpici order by getdate()),* from tb1
) t group by cpici'

exec(@s)

---结果
/*
cpici      cvlue1      cvlue2      cvlue3      cvlue4
---------- ----------- ----------- ----------- -----------
T501       31          33          5           NULL
T502       3           22          3           NULL
T503       53          44          50          23
警告: 聚合或其他 SET 操作消除了空值。

(3 行受影响)

*/

[zhurutang11]

[Quote=引用 2 楼 xys_777 的回复:]
SQL code
if object_id('tb1') is not null drop table tb1
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values……
[/Quote]
很好 ！顶了！

[obuntu]

[Quote=引用 2 楼 xys_777 的回复:]

SQL code
if object_id('tb1') is not null drop table tb1
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values('T501',……
[/Quote]


强

[999朵玫瑰]

学习了。。。