create table #SHU_TBL
(
id varchar(10),
s_id varchar(10)
)
insert into #SHU_TBL select '001','01'
union all select '002','02'
union all select '003','03'create table #SYS_TBL
(
s_id varchar(20),
flg int,
[name] varchar(20)
)
insert into #SYS_TBL select '01',1,'aa'
union all select '01',2,'ab'
union all select '01',3,'ac'
union all select '02',1,'ba'
union all select '02',2,'bb'
union all select '02',3,'bc'
union all select '03',1,'ca'
union all select '03',2,'cb'
union all select '03',3,'cc'create table #TYPE_TBL
(
t_cd varchar(20),
t_name varchar(20)
)
insert into #TYPE_TBL select '01','tab'
union all select '02','tbb'
union all select '03','tcb'select id,s_id,t_name,
max(case when flg=1 then [name] else null end ) 'name1',
max(case when flg=2 then [name] else null end ) 'name2',
max(case when flg=3 then [name] else null end ) 'name3'
from (select S.id,L.*,B.t_name from #SHU_TBL S join #SYS_TBL L on S.s_id=L.s_id
join #TYPE_TBL B on B.t_cd=S.s_id) T
group by id,s_id,t_name
id s_id t_name name1 name2 name3
---------- -------------------- -------------------- -------------------- -------------------- --------------------
001 01 tab aa ab ac
002 02 tbb ba bb bc
003 03 tcb ca cb cc
(
id varchar(10),
s_id varchar(10)
)
insert into #SHU_TBL select '001','01'
union all select '002','02'
union all select '003','03'create table #SYS_TBL
(
s_id varchar(20),
flg int,
[name] varchar(20)
)
insert into #SYS_TBL select '01',1,'aa'
union all select '01',2,'ab'
union all select '01',3,'ac'
union all select '02',1,'ba'
union all select '02',2,'bb'
union all select '02',3,'bc'
union all select '03',1,'ca'
union all select '03',2,'cb'
union all select '03',3,'cc'create table #TYPE_TBL
(
t_cd varchar(20),
t_name varchar(20)
)
insert into #TYPE_TBL select '01','tab'
union all select '02','tbb'
union all select '03','tcb'select id,s_id,t_name,
max(case when flg=1 then [name] else null end ) 'name1',
max(case when flg=2 then [name] else null end ) 'name2',
max(case when flg=3 then [name] else null end ) 'name3'
from (select S.id,L.*,B.t_name from #SHU_TBL S join #SYS_TBL L on S.s_id=L.s_id
join #TYPE_TBL B on B.t_cd=S.s_id) T
group by id,s_id,t_name
id s_id t_name name1 name2 name3
---------- -------------------- -------------------- -------------------- -------------------- --------------------
001 01 tab aa ab ac
002 02 tbb ba bb bc
003 03 tcb ca cb cc
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
select UserName,sum(case when Subject= '数学' then Score else 0 end) [数学],sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '语文' then Score else 0 end) [语文]
declare @sql varchar(1000)
set @sql='select UserName'
select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)aset @sql = @sql + ' from tb group by UserName'
print @sql
exec(@sql)
--讲解:--这个是第一次执行
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学],
--这个是第二次
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] ,
--这个是第三次
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , max(case 课程 when '语文' then 分数 else 0 end) [语文]
--这个的数量来自于
(select distinct 课程 from tb)--这里只有3们课程create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
我只说一个地方
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
你把这个看懂就明白了
例如当@id=1
select @str = @str + ',' + cast(value as varchar) from tb where id = 1
把满足id=1的str值通过','累加
当id是动态的就是1或者2...是当满足1的查询完了,把值付给str之后
在查询满足2的直到所有的ID完为止
这样明白了吧
insert into shu_tbl select '001','01','02'
insert into shu_tbl select '002','02','02'
insert into shu_tbl select '003','03','01'
create table SYS_TBL (s_id varchar(10),flg int,name varchar(10))
insert into sys_tbl select '01',1,'aa'
insert into sys_tbl select '01',2,'ab'
insert into sys_tbl select '01',3,'ac'
insert into sys_tbl select '02',1,'ba'
insert into sys_tbl select '02',2,'bb'
insert into sys_tbl select '02',3,'bc'
insert into sys_tbl select '03',1,'ca'
insert into sys_tbl select '03',2,'cb'
insert into sys_tbl select '03',3,'cc'
create table TYPE_TBL (t_cd varchar(10),t_name varchar(10))
insert into type_tbl select '01','tab'
insert into type_tbl select '02','tbb'
insert into type_tbl select '03','tcb'
go
select a.id,a.s_id,b.name as name1,c.name as name2,d.name as name3,e.t_name as [type_name]
from shu_tbl a
left join sys_tbl b on a.s_id=b.s_id
left join sys_tbl c on a.s_id=c.s_id
left join sys_tbl d on a.s_id=d.s_id
inner join type_tbl e on a.t_cd=e.t_cd
where b.flg=1 and c.flg=2 and d.flg=3
go
drop table shu_tbl,sys_tbl,type_tbl
/*
id s_id name1 name2 name3 type_name
---------- ---------- ---------- ---------- ---------- ----------
001 01 aa ab ac tbb
002 02 ba bb bc tbb
003 03 ca cb cc tab*/
/////////////////
改天加分再给你补分。。呵呵