sql server 转置 和实现随机分配和一串代码的含义拼在一行
1、sql server 转置很容易搜到方法，一般需要手动写转置的列项，如果多时会比较烦,下面试了省事的方法：
--案例需求数据
----方法一：
if object_id('tempdb.dbo.#student') is not null drop table #student
select dcdate,student,score
into #student
from ABC.dbo.t_student_mx
where dcdate='20180201'
；
if object_id('tempdb.dbo.#student') is not null drop table #student
select dcdate,student,score
into #student
from ABC.dbo.t_student_mx
where dcdate='20180201'
；
declare @sql varchar(8000)
declare @sql2 varchar(8000)
select @sql = isnull(@sql+',','') + '['+student+']'
from #student
select @sql = isnull(@sql+',','') + '['+student+']'
from #student
print @sql
set @sql2 = 'select * into a from #student pivot(sum(score) for student in ('+@sql +')) t'
exec(@sql2)----这里存入了实体表a
;
---方法二：是通常用的方法
select *
into #b
from #student pivot(sum(score) for student in ([张三],[李四],[王五],[赵六],[陆七],[冯宝宝],[张楚兰],[王也])
exec(@sql2)----这里存入了实体表a
;
---方法二：是通常用的方法
select *
into #b
from #student pivot(sum(score) for student in ([张三],[李四],[王五],[赵六],[陆七],[冯宝宝],[张楚兰],[王也])
2、还要可能会遇到随机分配的情况，比如来了几个学生，随机分配给三位老师，我试了下面这些：
----随机的方法1：如果有一群学生需要随机分配给三位老师：可以这样：先把学生和老师表关联就会出现一个人分别对应几个学生而且三个老师的编码随机（没有排序）
---第二步：一个学生就可以对随机编码的老师按照最大编码或最小编码选择出唯一一个对应人，因为编码是随机的
drop table #e
select distinct a.student,b.teacher,row_number() over (order by newid()) bm
into #e
from ABC.dbo.student a, ABC.dbo.v_teacher b
where b.teacher in ('黄磊','黄渤','张三')
;
select a.*
from #e a
where a.bm=(select max(bm) from #e z where a.student=z.student)
;
select teacher,count(1)
from #d
group by teacher
;
----随机的方法2：用游标对每个学生在三个老师中随机选择假设一个学生:直接对每个学生随机出一个老师，因为学生唯一对应一个老师而员工是一对多。也可以固定随机选出数，一个员工随机几百个。
if object_id('tempdb.dbo.#student_yg') is not null drop table #student_yg
select top 1 teacher,a.student,newid() id
into #student_yg
from ABC.dbo.student a, ABC.dbo.v_teacher b
where b.teacher in ('黄磊','黄渤','黄渤')
order by newid()
---第二步：一个学生就可以对随机编码的老师按照最大编码或最小编码选择出唯一一个对应人，因为编码是随机的
drop table #e
select distinct a.student,b.teacher,row_number() over (order by newid()) bm
into #e
from ABC.dbo.student a, ABC.dbo.v_teacher b
where b.teacher in ('黄磊','黄渤','张三')
;
select a.*
from #e a
where a.bm=(select max(bm) from #e z where a.student=z.student)
;
select teacher,count(1)
from #d
group by teacher
;
----随机的方法2：用游标对每个学生在三个老师中随机选择假设一个学生:直接对每个学生随机出一个老师，因为学生唯一对应一个老师而员工是一对多。也可以固定随机选出数，一个员工随机几百个。
if object_id('tempdb.dbo.#student_yg') is not null drop table #student_yg
select top 1 teacher,a.student,newid() id
into #student_yg
from ABC.dbo.student a, ABC.dbo.v_teacher b
where b.teacher in ('黄磊','黄渤','黄渤')
order by newid()
truncate table #student_yg
declare @student varchar(30)
declare cur cursor for
SELECT student FROM ABC.dbo.student_20180711
open cur
fetch next from cur into @student
while @@FETCH_STATUS=0
begin
print @student
exec( 'insert into #student_yg select top 1 teacher,a.student,newid() id from ABC.dbo.v_teacher b,ABC.dbo.student a
where b.teacher in (''黄磊'',''黄渤'',''黄渤'') and a.student='+@student+' order by newid()')
fetch next from cur into @student
end
close cur
deallocate cur
;
select teacher,count(1)
from #student_yg
group by teacher
declare @student varchar(30)
declare cur cursor for
SELECT student FROM ABC.dbo.student_20180711
open cur
fetch next from cur into @student
while @@FETCH_STATUS=0
begin
print @student
exec( 'insert into #student_yg select top 1 teacher,a.student,newid() id from ABC.dbo.v_teacher b,ABC.dbo.student a
where b.teacher in (''黄磊'',''黄渤'',''黄渤'') and a.student='+@student+' order by newid()')
fetch next from cur into @student
end
close cur
deallocate cur
;
select teacher,count(1)
from #student_yg
group by teacher
3、在数据查询时会遇到一些编码：比如123jw：1对应：好 2：吃，3；玩等，如何把123jw 翻印出来并用'，'隔开呢.
----用xml来直接拼
select distinct a.code,name=(stuff((select ','+code_name from ABC.dbo.code_name z where charindex(replace(z.code,' ',''),a.code)>0 for xml path('')),1,1,''))
from #b a
group by a.code
select distinct a.code,name=(stuff((select ','+code_name from ABC.dbo.code_name z where charindex(replace(z.code,' ',''),a.code)>0 for xml path('')),1,1,''))
from #b a
group by a.code
慢慢学习，一步一步成长，从坚持开始，都是特别基础和简单的东西，望大家指导----
