表中有两个字段如下(行数不定)
f_id f_type
1 a
2 b
3 c
4 d
5 e
6 f 想变成如下形式
1 2 3 4 5 6
a b c d e f目的就是为了显示方便,用sql语句在显示的时候行列互换
f_id f_type
1 a
2 b
3 c
4 d
5 e
6 f 想变成如下形式
1 2 3 4 5 6
a b c d e f目的就是为了显示方便,用sql语句在显示的时候行列互换
解决方案 »
- mysql转换成sqlserver2005数据库
- 如何这样Select数据?
- 知道怎么测一个字符型列的长度么???
- 请教无法删除强制订阅的问题,在线等!
- 急!求SQL语句
- delphi 中如何用数据流导excel
- select distinct top 10 percent qy_name,qy_add from biao order by newid()随机取记录,distinct无效,怎么解决?顶者有分!
- 求大神赐sql语句
- 请教各位大侠,关于在pb中进行数据库备份难题!恳请高手不吝赐教!
- SQL SERVER 2008 执行语句一直在调试
- 对大数据流量的存储和优化
- 1000万条表的存储和优化(思路)
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
--生成中间数据表
declare @s varchar(8000)
set @s='create table test2(a varchar(20)'
select @s=@s+','+A+' varchar(10)' from test1
set @s=@s+')'
exec(@s)--借助中间表实现行列转换
declare @name varchar(20)declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid>1 order by colidopen t_cursorfetch next from t_cursor into @namewhile @@fetch_status=0
begin
exec('select '+@name+' as t into test3 from test1')
set @s='insert into test2 select '''+@name+''''
select @s=@s+','''+rtrim(t)+'''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
--查看行列互换处理结果
select * from test2/*
a x y z
---- ---- ---- ----
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12
*/
--删除测试数据
drop table test1,test2
indust 200301 200302 200303
---------- ---------- ---------- ----------
a 111 222 333
b 444 555 666
c 777 888 999
d 789 910 012
--要求得到结果
日期 a b c d
------ ---- ---- ---- ----
200301 111 444 777 789
200302 222 555 888 910
200303 333 666 999 012
--*/--创建测试表
create table test(indust varchar(10)
,[200301] varchar(10)
,[200302] varchar(10)
,[200303] varchar(10))
insert test select 'a','111','222','333'
union all select 'b','444','555','666'
union all select 'c','777','888','999'
union all select 'd','789','910','012'
go--数据处理
declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000)
select @f1='',@f2='',@f3=''
select @f1=@f1+',['+indust+']='''+[200301]+''''
,@f2=@f2+','''+[200302]+''''
,@f3=@f3+','''+[200303]+''''
from test
exec('select 日期=''200301'''+@f1
+' union all select ''200302'''+@f2
+' union all select ''200303'''+@f3)
go
--删除测试表
select * from test
drop table test/*--测试结果
日期 a b c d
------ ---- ---- ---- ----
200301 111 444 777 789
200302 222 555 888 910
200303 333 666 999 012
--*/
+' union all select ''200302'''+@f2
+' union all select ''200303'''+@f3)这条语句是什么意思
from tname
pivot(max(f_type)for f_id in([1],[2],[3],[4],[5]))as a