如何将下面的表转换成上面的,弄了半天都没弄出来,不想用循环来做.
------解决方案--------------------
- 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)[email protected],@[email protected]+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