请高人指教:
有一个表a,它的纪录如下:
name namevalue
a1 00
aa 01
ab 02
.....
先想根据表a生成一个新表b,表b的结构及纪录如下
a1 aa ab ......
00 01 02 ......
请问,我该如何实现?
有一个表a,它的纪录如下:
name namevalue
a1 00
aa 01
ab 02
.....
先想根据表a生成一个新表b,表b的结构及纪录如下
a1 aa ab ......
00 01 02 ......
请问,我该如何实现?
drop table #
Go
Create table #(name nvarchar(2),namevalue nvarchar(2))
Insert #
select N'a1',N'00' union all
select N'aa',N'01' union all
select N'ab',N'02'
Godeclare @s nvarchar(max)
set @s=''select @s=@s+',max(case Name when '''+Name+''' then NameValue end)['+Name+']'
from #set @s=Stuff(@s,1,1,'')select @s='select '+@s+'
from # '
exec (@s)
---- ---- ----
00 01 02
if object_id('tempdb..#') is not null
drop table #
go
create table #(name nvarchar(2),namevalue nvarchar(2))
Insert #
select N'a1',N'00' union all
select N'aa',N'01' union all
select N'ab',N'02'
go
if object_id('sp_pivot') is not null
drop proc sp_pivot
go
create procedure sp_pivot
as
begin
declare @c_sql nvarchar(4000),@i_sql nvarchar(4000),@s_sql nvarchar(4000)
select @c_sql='create table #t ('
select @i_sql='insert into #t values('
select @s_sql='select * from #t'
select @C_sql=@C_sql + quotename([name]) + ' nvarchar(50),', @i_sql=@i_sql +''''+namevalue + ''',' from #
select @c_sql=left(@c_sql,len(@c_sql)-1)
,@i_sql=left(@i_sql,len(@i_sql)-1)
select @c_sql= @c_sql +')',@i_sql= @i_sql +')'
exec( @c_sql+';'+@i_sql+';'+@s_sql)
end
go
exec sp_pivot
Set Nocount On
if not object_id('Test') is null
drop table Test
Go
Create table Test([name] nvarchar(2),[namevalue] nvarchar(2))
Insert Test
select N'a1',N'00' union all
select N'aa',N'01' union all
select N'ab',N'02'
Go
Declare @Sql nvarchar(Max)
Set @Sql='Select * From Test Pivot(Max(namevalue) For name In(' +
Stuff((Select Distinct ','+Quotename(name) From Test For Xml Path('')),1,1,'')
Exec (@Sql+N')) b')/*
a1 aa ab
---- ---- ----
00 01 02
*/