sql语句行转列 怎么转啊
ID name sex num
1 tom 男 2
2 tom 男 3
3 tom 男 4
4 tom 男 5
这是转换后的结果
ID name sex num1 num2 num3 num4
1 tom 男 2 3 4 5 展开
--声明变量
declare @sql varchar(1000),@num_data int,@num_all varchar(2000),@num_num int,@table_sql varchar(2000)
set @num_num=0
--判断并创建表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[records]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[records]
create table records(
[id] int,
[name] varchar(50),
[sex] varchar(10),
[num] int
)
--插入数据
insert into records values(1,'tom','男',2)
insert into records values(1,'tom','男',3)
insert into records values(1,'tom','男',4)
insert into records values(1,'tom','男',5)
--全选表中数据
select * from records
--全选num列数据
select num as '数据'from records
--释放游标
deallocate select_num
--为‘select num from records’建立游标
declare select_num scroll cursor for select num as 'shuju' from records
--打开游标
open select_num
--获得第一条数据
fetch next from select_num into @num_data
set @num_all=convert(varchar,@num_data) + ','
set @num_num=@num_num+1;
--如果获取成功，继续获得数据
while @@fetch_status=0
begin
fetch next from select_num into @num_data
set @num_num=@num_num+1;
set @num_all=@num_all +convert(varchar,@num_data)+','
end
--关闭游标
close select_num
print @num_num
--set @num_num=@num_num-1;
declare @i int
set @i=1
print @num_num
print @i
set @table_sql='create table numall (id int,name varchar(50),sex varchar(10)'
print @table_sql
while @num_num>=1
begin
set @table_sql=@table_sql+',num'+convert(varchar,@i)+' int'
set @num_num=@num_num-1;
set @i=@i+1
end
set @table_sql=@table_sql+')'
print @table_sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[numall]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[numall]
exec(@table_sql)
declare @insert_sql varchar(2000)
set @insert_sql='insert into numall values(1,'+'''tom'','+'''男'''
print @insert_sql
open select_num
--获得第一条数据
fetch next from select_num into @num_data
set @insert_sql=@insert_sql+','+convert(varchar,@num_data)
--如果获取成功，继续获得数据
while @@fetch_status=0
begin
fetch next from select_num into @num_data
set @insert_sql=@insert_sql+','+convert(varchar,@num_data)
end
set @insert_sql=@insert_sql+')'
print @insert_sql
exec(@insert_sql)
--insert into numall values(1,'tom','男',2,3,4,5,5)
select * from numall
试试吧，数据虽然有点出入，但已经说明问题了！！！
go
--> -->
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([ID] int,[name] nvarchar(3),[sex] nvarchar(1),[num] int)
Insert #1
select 1,N'tom',N'男',2 union all
select 2,N'tom',N'男',3 union all
select 3,N'tom',N'男',4 union all
select 4,N'tom',N'男',5
Go
DECLARE @i VARCHAR(3),@s NVARCHAR(2000)
Select TOP 1 @i=COUNT(*),@s='' from #1 GROUP BY [name],[sex] ORDER BY COUNT(*) desc
WHILE @i>0
SELECT @s=',[num'+@i+']=max(case when row='+@i+' then num end)'+@s,@i=@i-1
exec('select [name],[sex]'+@s+'FROM (SELECT *,(SELECT COUNT(*) FROM #1 WHERE Name=a.NAME AND sex=a.sex AND ID<=a.ID) AS row FROM #1 AS a)Tab group by [name],[sex]')
/*
name sex num1 num2 num3 num4
tom 男 2 3 4 5
*/
生成的语句：
select
[name],[sex],[num1]=max(case when row=1 then num end),[num2]=max(case when row=2 then num end),
[num3]=max(case when row=3 then num end),[num4]=max(case when row=4 then num end)
FROM
(SELECT *,(SELECT COUNT(*) FROM #1 WHERE Name=a.NAME AND sex=a.sex AND ID<=a.ID) AS row FROM #1 AS a)Tab
group by [name],[sex]
那么只用 sql 语句处理， 恐怕是有的麻烦了
可能要用存储过程来处理了。
可以参考下面的贴子。
动态行列转换处理 例子:
http://hi.baidu.com/wangzhiqing999/blog/item/aea4a6d7ea136c1a3af3cfd0.html
看max(电话)
sum(case when num=2 then 2 end) as num2,
sum(case when num=3 then 3 end) as num3,
sum(case when num=4 then 4 end) as num4,
sum(case when num=5 then 5 end) as num5
from 表名 group by name,sex
转换前的数据条数是不固定的
栏位是不是固定的呢？是不是只有 num2,num3,num4,num5 ?
如果是语句就没有问题，因为已经做了gorup by
如果不是那就尽量多写一些， num6,num7....., 如果还不行，那就只能用存储过程 + 动态语句实现了
