/*----------把create前的括号去掉--------------*/
[create] table tab_1
(myType varchar(20),myValue1 int,myValue2 int)insert tab_1 values ('A',3,100)
insert tab_1 values ('A',4,100)
insert tab_1 values ('B',4,200)
insert tab_1 values ('C',5,300)
insert tab_1 values ('C',5,300)
go
create function f_he(@col1 varchar(10),@col2 int)
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+','+cast(myValue1 as varchar) from tab_1 where myType=@col1 and myValue2=@col2
return(stuff(@sql,1,1,''))
end
go
/*---------------开始程序------------------*/
declare @i int,@s varchar(1000)
set @i=96
select myType,myValue1=dbo.f_he(myType,myValue2),myValue2 into # from tab_1 group by myType,myValue2
while @@rowcount>0
begin
select @i=@i+1,
@s='alter table # add myValue1_'+char(@i)+' varchar(10)'
exec(@s)
set @s='update # set myValue1_'+char(@i)+'
=left(myValue1,charindex('','',myValue1+'','')-1),
myValue1=stuff(myValue1,1,charindex('','',myValue1+'',''),'''')
where myValue1>'''''
exec(@s)
end
set @s='alter table # drop column myValue1,myValue1_'+char(@i)
exec(@s)
select myType,isnull(myValue1_a,0) as myValue1_a,isnull(myValue1_b,0) as myValue1_b, myValue2 from #
drop table #
drop function f_he
drop table tab_1
myType myValue1_a myValue1_b myValue2
-------------------- ---------- ---------- -----------
A 3 4 100
B 4 0 200
C 5 5 300(所影响的行数为 3 行)
[create] table tab_1
(myType varchar(20),myValue1 int,myValue2 int)insert tab_1 values ('A',3,100)
insert tab_1 values ('A',4,100)
insert tab_1 values ('B',4,200)
insert tab_1 values ('C',5,300)
insert tab_1 values ('C',5,300)
go
create function f_he(@col1 varchar(10),@col2 int)
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+','+cast(myValue1 as varchar) from tab_1 where myType=@col1 and myValue2=@col2
return(stuff(@sql,1,1,''))
end
go
/*---------------开始程序------------------*/
declare @i int,@s varchar(1000)
set @i=96
select myType,myValue1=dbo.f_he(myType,myValue2),myValue2 into # from tab_1 group by myType,myValue2
while @@rowcount>0
begin
select @i=@i+1,
@s='alter table # add myValue1_'+char(@i)+' varchar(10)'
exec(@s)
set @s='update # set myValue1_'+char(@i)+'
=left(myValue1,charindex('','',myValue1+'','')-1),
myValue1=stuff(myValue1,1,charindex('','',myValue1+'',''),'''')
where myValue1>'''''
exec(@s)
end
set @s='alter table # drop column myValue1,myValue1_'+char(@i)
exec(@s)
select myType,isnull(myValue1_a,0) as myValue1_a,isnull(myValue1_b,0) as myValue1_b, myValue2 from #
drop table #
drop function f_he
drop table tab_1
myType myValue1_a myValue1_b myValue2
-------------------- ---------- ---------- -----------
A 3 4 100
B 4 0 200
C 5 5 300(所影响的行数为 3 行)
同时指出,这个表有不合理的地方,两个
C 5 300
C 5 300
是重复数据,这是非常不合理的!
/*---------开始程序-------------*/
declare @i int,@s varchar(1000)
set @i=96
select myType,myValue1=dbo.f_he(myType,myValue2),myValue2 into # from tab_1 group by myType,myValue2
set @s='alter table # add myValue1_a int,myValue1_b int'
exec(@s)update # set myValue1_a=left(myValue1,charindex(',',myValue1+',')-1),
myValue1_b=stuff(myValue1,1,charindex(',',myValue1+','),'')/*-----------显示语句-------------*/
select myType,isnull(myValue1_a,0) as myValue1_a,isnull(myValue1_b,0) as myValue1_b, myValue2 from #myType myValue1_a myValue1_b myValue2
-------------------- ----------- ----------- -----------
A 3 4 100
B 4 0 200
C 5 5 300(所影响的行数为 3 行)
case when ((select top 1 myValue1 from tab_1
where myType=a.myType order by myValue1 desc)=(select top 1 myValue1 from tab_1
where myType=a.myType order by myValue1) and (select count(1) from tab_1 where myType=a.myType)=1) then 0
else (select top 1 myValue1 from tab_1 where myType=a.myType order by myValue1 desc) end as myValue1_b,
max(myValue2) as myValue2 from tab_1 a group by myType
a.myType 是什么
to samfeng_2003(风云) :我没看懂你的方法,能否解释一下。
select myType,case when (select count(1) from tab_1 where myType=a.myType)>1 then
(select top 1 myValue1 from tab_1 where myType=a.myType order by myValue1)+(select top 1 myValue1 from tab_1 where myType=a.myType order by myValue1 desc)
else
(select top 1 myValue1 from tab_1 where myType=a.myType order by myValue1)+'0'
end as [myValue1_a myValue1_b ]
,max(myValue2) as myValue2 from tab_1 a group by myType
把求和去掉结果为
myType myValue1_a myValue1_b myValue2
A 3 4 100
B 4 4 200
C 5 5 300
我要的结果是
myType myValue1_a myValue1_b myValue2
A 3 4 100
B 4 0 200
C 5 5 300
用myValue1 排序的方法不行,因为不是每个myType都有两个记录,例如B只有一个。
case when (select count(1) from tab_1 where myType=a.myType)>1 then
cast((select top 1 myValue1 from tab_1 where myType=a.myType order by myValue1)as varchar(10))+' '+cast((select top 1 myValue1 from tab_1 where myType=a.myType order by myValue1 desc) as varchar(10))
else
(select top 1 myValue1 from tab_1 where myType=a.myType order by myValue1)+'0'
end as [myValue1_a myValue1_b ]
,max(myValue2) as myValue2 from tab_1 a group by myType