SQL行转列有关问题

SQL行转列问题
如何将下面的表转换成上面的,弄了半天都没弄出来,不想用循环来做.

[解决办法]

SQL code

set nocount onselect * 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 aouter 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 intSelect TOP 1 @i=COUNT(*),@s='' from 表1 GROUP BY Code ORDER BY COUNT(1) descSET @j=65SET @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 @sEXEC(@s+'FROM (SELECT *,row=row_number()over(partition by Cold order by Code)FROM 表1 AS a)tGROUP 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 dfrom   (select id=row_number()over(partition by code order by Code),* from tb)tgroup by   code
[解决办法]
如最大列数知道的话,可以按下面的方式处理:
SQL code
create table tb(col varchar(10),val int)insert into tb select 'T',352insert into tb select 'Q',224insert into tb select 'L',142insert into tb select 'T',824insert into tb select 'T',457insert into tb select 'Q',634insert into tb select 'Q',74insert into tb select 'T',134insert into tb select 'L',536goselect col,[A],[B],[C],[D] from(select *,char(ROW_NUMBER()over(partition by col order by (select 1))+64)rn from tb)Tpivot (sum(val) for rn in([A],[B],[C],[D]))b/*col        A           B           C           D---------- ----------- ----------- ----------- -----------L          142         536         NULL        NULLQ          634         74          224         NULLT          352         824         457         134(3 行受影响)*/godrop table tb