http://expert.csdn.net/Expert/topic/2440/2440306.xml?temp=.9671595--===========================================回复人： txlicenhe(马可) ( ) 信誉：129 2003-11-8 13:09:16 得分:0
----------------------
1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)想变成姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table testeg2:
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A--=========================================
2:
/*********** 行转列 *****************/
测试：
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)declare @ varchar(8000)
set @=''
select @=@+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '+@+' from t1')a
-----------
15
9
1
0
1
2
2
0--==========================================3:
将结果矩阵转置。
http://expert.csdn.net/Expert/topic/2390/2390314.xml?temp=.4681055
老衲：
if exists (select * from sysobjects where id = object_id('proc_sky_blue') and xtype ='P')
drop proc proc_sky_blue
go
create proc proc_sky_blue (@tablename varchar(200))
as
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
end
endgo
----------------分析
declare @tablename varchar(200)
set @tablename='table1'
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
select @insertsql
--exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
drop table #tmp
end
end
----------------------
1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)想变成姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table testeg2:
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A--=========================================
2:
/*********** 行转列 *****************/
测试：
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)declare @ varchar(8000)
set @=''
select @=@+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '+@+' from t1')a
-----------
15
9
1
0
1
2
2
0--==========================================3:
将结果矩阵转置。
http://expert.csdn.net/Expert/topic/2390/2390314.xml?temp=.4681055
老衲：
if exists (select * from sysobjects where id = object_id('proc_sky_blue') and xtype ='P')
drop proc proc_sky_blue
go
create proc proc_sky_blue (@tablename varchar(200))
as
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
end
endgo
----------------分析
declare @tablename varchar(200)
set @tablename='table1'
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
select @insertsql
--exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
drop table #tmp
end
end
declare @s varchar(8000)
set @s=''
select @s=@s+',交期_'+cast(AdjustNum as varchar)
+'=max(case AdjustNum when '+cast(AdjustNum as varchar)
+' then DeliveryDate else '''' end),数量_'+cast(AdjustNum as varchar)
+'=isnull(cast(sum(case AdjustNum when '++cast(AdjustNum as varchar)
+' then DeliveryQty end) as varchar),'''')'
from OrderAdjust
group by AdjustNum
exec('select OrderNo,ItemNo'+@s+'
from (
select OrderNo,ItemNo
,DeliveryDate=convert(char(10),DeliveryDate,120)
,DeliveryQty,AdjustNum,gid=(
select sum(1) from OrderAdjust
where OrderNo=a.OrderNo and ItemNo=a.ItemNo
and AdjustNum=a.AdjustNum
and DeliveryDate<=a.DeliveryDate)
from OrderAdjust a
)a group by OrderNo,ItemNo,gid
')
create table OrderAdjust(
OrderNo Varchar(10),--订单号
ItemNo Varchar(20), --产品号
DeliveryDate DateTime,--交期
DeliveryQty Numeric(9,0),--数量
AdjustNum Smallint --调整次数
)
insert OrderAdjust select '001','A01','2004-01-01',1000,1
union all select '001','A01','2004-02-01',2000,1
union all select '001','A01','2004-01-10',500, 2
union all select '001','A01','2004-01-15',500, 2
union all select '001','A01','2004-01-10',200, 3
union all select '001','A01','2004-01-20',300, 3
union all select '001','A01','2004-01-15',500, 3
union all select '002','B01','2004-03-01',5000,1
union all select '002','C01','2004-03-01',4000,1
go--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',交期_'+cast(AdjustNum as varchar)
+'=max(case AdjustNum when '+cast(AdjustNum as varchar)
+' then DeliveryDate else '''' end),数量_'+cast(AdjustNum as varchar)
+'=isnull(cast(sum(case AdjustNum when '++cast(AdjustNum as varchar)
+' then DeliveryQty end) as varchar),'''')'
from OrderAdjust
group by AdjustNum
exec('select OrderNo,ItemNo'+@s+'
from (
select OrderNo,ItemNo
,DeliveryDate=convert(char(10),DeliveryDate,120)
,DeliveryQty,AdjustNum,gid=(
select sum(1) from OrderAdjust
where OrderNo=a.OrderNo and ItemNo=a.ItemNo
and AdjustNum=a.AdjustNum
and DeliveryDate<=a.DeliveryDate)
from OrderAdjust a
)a group by OrderNo,ItemNo,gid
')
go--删除测试
drop table OrderAdjust/*--测试结果
OrderNo ItemNo 交期_1 数量_1 交期_2 数量_2 交期_3 数量_3
---------- ------- ---------- ---------- ---------- --------- ---------- -----
001 A01 2004-01-01 1000 2004-01-10 500 2004-01-10 200
001 A01 2004-02-01 2000 2004-01-15 500 2004-01-15 500
001 A01 2004-01-20 300
002 B01 2004-03-01 5000
002 C01 2004-03-01 4000
--*/
set @s=''
select @s=@s+',交期_'+cast(AdjustNum as varchar)
+'=max(case AdjustNum when '+cast(AdjustNum as varchar)
+' then DeliveryDate else '''' end),数量_'+cast(AdjustNum as varchar)
+'=isnull(cast(sum(case AdjustNum when '++cast(AdjustNum as varchar)
+' then DeliveryQty end) as varchar),'''')'
from OrderAdjust
group by AdjustNum
exec('select OrderNo=case gid when 1 then OrderNo else '''' end
,ItemNo=case gid when 1 then ItemNo else '''' end'+@s+'
from (
select OrderNo,ItemNo
,DeliveryDate=convert(char(10),DeliveryDate,120)
,DeliveryQty,AdjustNum,gid=(
select sum(1) from OrderAdjust
where OrderNo=a.OrderNo and ItemNo=a.ItemNo
and AdjustNum=a.AdjustNum
and DeliveryDate<=a.DeliveryDate)
from OrderAdjust a
)a group by OrderNo,ItemNo,gid
')