sql如何把一列数据拆分成一行多列

Question

表 recordID  Class sex  Grade  Score             Total
1    2     0     3    0,9,8,7,6,1         35SQL语句如何变成
ID  Class sex  Grade  a1  a2  a3  a4  a5  a6        Total
1    2     0     3    0    9   8  7   6   1         35
有大大会么 =。=

Answer 1

--> 测试数据: [record]
if object_id('[record]') is not null drop table [record]
go 
create table [record] (ID int,Class int,sex int,Grade int,Score varchar(50),Total int )
insert into [record]
select 1,2,0,3,'0,9,8,7,6,1',35if object_id('tb')is not null drop table tbdeclare @s varchar(1000)select ID, Class, sex, Grade ,Total,Score=substring(score, number,charindex(',', score+',',number)-number) ,n=row_number()over(partition by id order by id)
into tb
from [record],master..spt_values 
where type='p' and number>0
and substring(','+score,number,1)=','select  @s=isnull(@s,' ')+', max(case when number='+ltrim(n)+' then score end) [A'+ltrim(n)+']'
from (select distinct n from tb)t  set @s=' select ID, Class, sex, Grade ,Total '+ @s +  
' from tb,
master..spt_values 
where type=''p'' and number>0
and n=number
group by ID, Class, sex, Grade ,Total'--print @s
exec(@s)ID          Class       sex         Grade       Total       A1                                                 A2                                                 A3                                                 A4                                                 A5                                                 A6
----------- ----------- ----------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           2           0           3           35          0                                                  9                                                  8                                                  7                                                  6                                                  1
警告: 聚合或其他 SET 操作消除了空值。(1 行受影响)