数据的原始状态如下:
ID PR CON OP SC
001 p c 差 6
001 p c 好 2
001 p c 一般 4
002 w e 差 8
002 w e 好 7
002 w e 一般 1
===========================
用SQL语句实现,变成如下的数据
ID PR CON OPS
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)
ID PR CON OP SC
001 p c 差 6
001 p c 好 2
001 p c 一般 4
002 w e 差 8
002 w e 好 7
002 w e 一般 1
===========================
用SQL语句实现,变成如下的数据
ID PR CON OPS
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)
CREATE TABLE tb([ID] VARCHAR(10), [PR] varchar(10), [CON] varchar(10), [OP] varchar(10), [SC] int)
INSERT INTO tb
SELECT 001, 'p', 'c', '差', 6
UNION ALL SELECT 001, 'p', 'c', '好', 2
UNION ALL SELECT 001, 'p', 'c', '一般', 4
UNION ALL SELECT 002, 'w', 'e', '差', 8
UNION ALL SELECT 002, 'w', 'e', '好', 7
UNION ALL SELECT 002, 'w', 'e', '一般', 1GO
CREATE FUNCTION GetRet(@ID VARCHAR(10), @PR VARCHAR(10), @CON VARCHAR(10))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @RE VARCHAR(1000)
SET @RE = ''
SELECT @RE = @RE + OP + '(' + CAST(SC AS VARCHAR) + '),' FROM TB WHERE ID = @ID AND PR = @PR AND CON = @CON
SET @RE = LEFT(@RE, LEN(@RE) - 1)
RETURN @RE
END
GOSELECT T.ID, T.PR, T.CON, dbo.GetRet(T.ID, T.PR, T.CON) FROM (SELECT DISTINCT ID, PR, CON FROM tb)TDROP FUNCTION GetRet
DROP TABLE tb
(
@id varchar(10)
)
returns varchar(200)
as
begin
declare @r varchar(200)
set @r=''
select @r=@r+','+op+'('+cast(sc as varchar)_')' from tablename where id=@id
if @r<>''
set @r=stuff(@r,1,1,'')
return @r
end
go--调用
seelct ID,PR,CON,dbo.fn_ops(id) as OPS from tablename
group by ID,PR,CON--未测试
drop table tb
gocreate table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '差', 6)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '好', 2)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '一般', 4)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '差', 8)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '好', 7)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '一般', 1)
goif object_id('pubs..test') is not null
drop table test
go
select ID,PR,CON , OPS = op + '(' + cast(sc as varchar(10)) + ')' into test from tb--创建一个合并的函数
if object_id('pubs..f_hb') is not null
drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from testdrop table tb
drop table test--结果
id pr con OPS
---------- ---------- ---------- -------------------
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)(所影响的行数为 2 行)
select id,pr,con,
max(case op when '差' then sc else '' end)+
max(case op when '好' then sc else '' end)+
max(case op when '一般' then sc else '' end) as ops
from tablename
group by id,pr,con
insert #kk(ID, PR , CON ,OP , SC ) values('001', 'p' , 'c',' 差','6')
insert #kk(ID , PR , CON , OP , SC ) values('001' ,'p' , ' c' , '好' , '2')
insert #kk(ID , PR , CON , OP , SC ) values('001', 'p' ,' c ' , '一般' , '4')
insert #kk(ID, PR , CON , OP , SC ) values('002','w' , 'e' , '差' , '8')
insert #kk(ID , PR , CON, OP , SC ) values('002' ,'w' , ' e ' , '好' , '7')
insert #kk(ID , PR , CON , OP , SC ) values('002' ,'w' , ' e ' ,' 一般' , '1')
select * from #kk
declare @ww nvarchar(100),@ww1 nvarchar(100),@Sql nvarchar(100),@c int, @ic int,@id varchar(10)
set @ww=''
set @ww1=''
set @ic=1
select OP+'('+SC+')' from #kk
declare k cursor for
select distinct pr,id from #kk
open k
select @c=count(distinct pr) from #kkwhile @ic<=@c
Begin
fetch next from k into @ww1,@id
set @ww=''
set @Sql=N'select @ww=@ww+OP+''(''+SC+'')''+'','' from #kk where pr='''+@ww1+''''
exec sp_executesql @Sql,N'@ww Varchar(100) out',@ww out
Set @ic=@ic+1
select @id,@ww1,@ww
end
close k
deallocate k
drop table #kk