--测试数据
create table 表(列1 varchar(10),列2 varchar(10),列3 varchar(10))
insert 表 select 'majun','马俊','建银物业'
union all select 'majun','马俊','综合部'
union all select 'majun','马俊','工程部'
union all select 'majun','马马','综合部'
union all select 'majun','马马','工程部'
go
--处理函数
create function f_str(@列1 varchar(10),@列2 varchar(10))
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+','+列3 from 表
where 列1=@列1 and 列2=@列2
return(stuff(@s,1,1,''))
end
go
--调用函数实现查询
select 列1,列2,列3=dbo.f_str(列1,列2) from 表
group by 列1,列2
go
---------建表----------
create table tab_score
(
bid int identity(0,1) primary key ,--流水号
sid varchar(20) not null,--学生号
cid varchar(20) not null,--课程号
score int--成绩
)
insert into tab_score select's01','c01','90' union all select 's01','c02','92' union all select 's01','c03','93'
union all select 's02','c01','81' union all select 's02','c02','82'
/*---固定列的写法,后面的写法将是根据有几个课程id来动态组装中间的sum语句,然后加上头尾就成了,
理解了这种'静态'写法,剩下的只是'动态'组装中间sum语句的工作----*/
select * from tab_score
select sid,sum(case cid when 'c01' then score else '0' end) as 'c01',
sum(case cid when 'c02' then score else '0' end) as 'c02',
sum(case cid when 'c03' then score else '0' end) as 'c03'
from tab_score group by sid
-----'动态'列的写法,定义一个变量来组装中间的sum语句,其中用到子查询表(原来不用这方法一直会出现重复列)-------
declare @s varchar(1000)
set @s=''
select @s=@s+', sum(case cid when '+''''+ a.cid+''''+' then score else ''0'' end) as '+''''+a.cid+''''
from ( select distinct cid from tab_score) a
print @*
**ec('select sid'+@s+'from tab_score group by sid')