22,209
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(8000)
set @sql='insert into account_details(js_dh,js_fsbh,js_je,js_kh,js_sl,js_jssj,js_btbh) select js_dh,js_fsbh,js_je,js_kh,js_sl,js_jssj,js_btbh from (select tid'
select @sql=@sql+' , max(case colname when '''+ colname +''' then val1 else '''' end) ['+ colname +']'
from (select distinct colname from uuu_details) as a
set @sql=@sql+' from uuu_details group by tid) a'
exec(@sql)
以上代码直接复制过去执行就ok了,如果不报错那就没问题了。你的两个表示account_details和uuu_details对吧[/quote][/quote]
成功了就好,不用谢,帮助别人快乐自己嘛。这些都是举手之劳,有什么道理不帮助你呢
declare @sql varchar(8000)
set @sql='insert into account_details(js_dh,js_fsbh,js_je,js_kh,js_sl,js_jssj,js_btbh) select js_dh,js_fsbh,js_je,js_kh,js_sl,js_jssj,js_btbh from (select tid'
select @sql=@sql+' , max(case colname when '''+ colname +''' then val1 else '''' end) ['+ colname +']'
from (select distinct colname from uuu_details) as a
set @sql=@sql+' from uuu_details group by tid) a'
exec(@sql)
以上代码直接复制过去执行就ok了,如果不报错那就没问题了。你的两个表示account_details和uuu_details对吧[/quote]
declare @sql varchar(8000)
set @sql='insert into account_details(js_dh,js_fsbh,js_je,js_kh,js_sl,js_jssj,js_btbh) select js_dh,js_fsbh,js_je,js_kh,js_sl,js_jssj,js_btbh from (select tid'
select @sql=@sql+' , max(case colname when '''+ colname +''' then val1 else '''' end) ['+ colname +']'
from (select distinct colname from uuu_details) as a
set @sql=@sql+' from uuu_details group by tid) a'
exec(@sql)
以上代码直接复制过去执行就ok了,如果不报错那就没问题了。你的两个表示account_details和uuu_details对吧--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([tid] int,[colname] nvarchar(27),[val1] nvarchar(28))
Insert #T1
select 1,N'js_dh',N'1700099' union all
select 1,N'js_fsbh',N'1' union all
select 1,N'js_je',N'700.00' union all
select 1,N'js_kh',null union all
select 1,N'js_sl',null union all
select 1,N'js_jssj',N'2017-1-1' union all
select 1,N'js_btbh',N'001'
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select tid'
select @sql=@sql+' , max(case colname when '''+ colname +''' then val1 else '''' end) ['+ colname +']'
from (select distinct colname from #T1) as a
set @sql=@sql+' from #T1 group by tid'
exec(@sql)
use Tempdb
go
--> --> 听雨停了-->测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([tid] int,[colname] nvarchar(27),[val1] nvarchar(28))
Insert #tab
select 1,N'js_dh',N'1700099' union all
select 1,N'js_fsbh',N'1' union all
select 1,N'js_je',N'700.00' union all
select 1,N'js_kh',null union all
select 1,N'js_sl',null union all
select 1,N'js_jssj',N'2017-1-1' union all
select 1,N'js_btbh',N'001' UNION all
select 2,N'js_dh',N'1700088' union all
select 2,N'js_fsbh',N'1' union all
select 2,N'js_je',N'800.00' union all
select 2,N'js_kh','200' union all
select 2,N'js_sl','200' union all
select 2,N'js_jssj',N'2017-1-1' union all
select 2,N'js_btbh',N'001'
--测试数据结束
Select * from #tab
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[colname]+']' from #tab for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [js_btbh],[js_dh],[js_fsbh],[js_je],[js_jssj],[js_kh],[js_sl] from #tab pivot(max(val1)for [colname] in('+@name+'))a'
PRINT @sql
EXEC( @sql)