set nocount on
select * from tb
/*
name value
---- ---------
张三   1,2,3,4
李四   a,s,d,f,g
*/

select name,newcol from
(
select *,cast('<V>'+REPLACE(value,',','</V><V>')+'</V>' as XML)as bxml from tb
) as a
outer apply
(
  select C.value('.','nvarchar(10)') as newcol
  from a.bxml.nodes('/V') as T(C)
)as b
/*

name newcol
---- ----------
张三   1
张三   2
张三   3
张三   4
李四   a
李四   s
李四   d
李四   f
李四   g
*/

------解决方案--------------------
SQL code
DECLARE @s NVARCHAR(4000),@i int,@j int

Select TOP 1 @i=COUNT(*),@s='' from 表1 GROUP BY Code ORDER BY COUNT(1) desc
SET @j=65
SET @s='select Code'
WHILE @j<@i+65
    SELECT @s=',['+NCHAR(@j)+']=max(case when row='+RTRIM(@j)+' then Material end)'+@s,@j=@j+1
 PRINT @s
EXEC(@s+'FROM 
(SELECT *,row=row_number()over(partition by Cold order by Code)
FROM 表1 AS a
)t
GROUP BY Code')

------解决方案--------------------

SQL code
靠 是下面的弄成上面的

select
   code,
   max(case id when 1 then material else '' end) as a,
   max(case id when 2 then material else '' end) as b,  
   max(case id when 3 then material else '' end) as c,  
   max(case id when 4 then material else '' end) as d
from
   (select id=row_number()over(partition by code order by Code),* from tb)t
group by
   code

------解决方案--------------------
如最大列数知道的话,可以按下面的方式处理:
SQL code
create table tb(col varchar(10),val int)
insert into tb select 'T',352
insert into tb select 'Q',224
insert into tb select 'L',142
insert into tb select 'T',824
insert into tb select 'T',457
insert into tb select 'Q',634
insert into tb select 'Q',74
insert into tb select 'T',134
insert into tb select 'L',536
go
select col,[A],[B],[C],[D] from(
select *,char(ROW_NUMBER()over(partition by col order by (select 1))+64)rn from tb
)T
pivot (sum(val) for rn in([A],[B],[C],[D]))b
/*
col        A           B           C           D
---------- ----------- ----------- ----------- -----------
L          142         536         NULL        NULL
Q          634         74          224         NULL
T          352         824         457         134

(3 行受影响)

*/
go
drop table tb