日期:2014-05-18 浏览次数:20435 次
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 */
------解决方案--------------------
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')
------解决方案--------------------
靠 是下面的弄成上面的 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
------解决方案--------------------
如最大列数知道的话,可以按下面的方式处理:
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