竖表转换成横表

[bJiao]

谁有好的方法，最简便的把
把这样的结构
id field1 data flag
1 sex m 1
2 name li 1
3 class first 1
4 sex w 2
5 name wang 2
6 class second 2
换成
id sex name class
1 m li first
2 w wang second

[zjcxc]

delcare @s varchar(8000)
set @s=''
select @s=@s+',['+field1+']=max(case field1 when '''+field1+''' then data else '''' end)' from(select distinct field1 from 表) a
exec('select id=flag'+@s+' from 表 group by flag')

[txlicenhe]

http://expert.csdn.net/Expert/topic/2440/2440306.xml?temp=.6941645
[交流]行列转换

[zjcxc]

--写错了一个字
declare @s varchar(8000)
set @s=''
select @s=@s+',['+field1+']=max(case field1 when '''+field1+''' then data else '''' end)' from(select distinct field1 from 表) a
exec('select id=flag'+@s+' from 表 group by flag')

[zjcxc]

--下面是数据测试

--创建测试表
create table #t(id int,field1 varchar(10),data varchar(10),flag int)
insert into #t
select 1,'sex','m',1
union all select 2,'name','li',1
union all select 3,'class','first',1
union all select 4,'sex','w',2
union all select 5,'name','wang',2
union all select 6,'class','second',2

--数据处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+field1+']=max(case field1 when '''+field1+''' then data else '''' end)' from(select distinct field1 from #t) a
exec('select id=flag'+@s+' from #t group by flag')

go
--删除测试环境
drop table #t


/*--测试结果:

id class name sex
----------- ---------- ---------- ----------
1 first li m
2 second wang w

--*/

[伍子V5]

select flag,
sum(case when field1 like '%sex%' then data end) sex,
sum(case when field1 like '%name%' then data end) name,
sum(case when field1 like '%class%' then data end) class
from tablename group by flag

[realgz]

declare @exec varchar(8000)
set @exec=''
select @exec=@exec+'case when field1='''+field1+''' then data end as '+field1+',' from table
set @exec =left(@exec,len(@exec)-1)
set @exec='select distinct id ,'+@exec +' from table'
exec (@exec)

[bJiao]

zjcxc(邹建)
:
如果@s varchar(8000)大于8000个字符怎么办？

[zjcxc]

--那可以用下面的处理方法:

declare @sqlhead nvarchar(4000),@sqlend nvarchar(4000)
,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
,@i int,@ic varchar(20)

--生成数据处理临时表
select id=identity(int,0,1),gid=0
,a=',['+field1+']=max(case field1 when '''
+field1+''' then data else '''' end)'
into # (select distinct field1 from 表) a

--判断需要多少个变量来处理
select @i=max(len(a)) from #
set @i=3800/@i

--分组临时表
update # set gid=id/@i
select @i=max(gid) from #

--生成数据处理语句
select @sqlhead='''select id=flag'''
,@sqlend=''' from 表 group by flag'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''

while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1='@'+@ic+' nvarchar(4000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
+char(13)+@sql3
,@sql4=@sql4+',@'+@ic

select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
,@sql2=left(@sql2,len(@sql2)-1)+char(13)
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=substring(@sql4,2,4000)

--执行
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
)

--删除临时表
drop table #

[zjcxc]

--上面的,语句头部分少写了一个from.

declare @sqlhead nvarchar(4000),@sqlend nvarchar(4000)
,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
,@i int,@ic varchar(20)

--生成数据处理临时表
select id=identity(int,0,1),gid=0
,a=',['+field1+']=max(case field1 when '''
+field1+''' then data else '''' end)'
into # from (select distinct field1 from 表) a

[lcq9732]

就是交叉数据表