588
社区成员
发帖
与我相关
我的任务
分享
declare @prepare int,@handle int,@arg xml
create table #con(id int identity,rowid int,colname varchar(10),val nvarchar(max))
;with a as (
select '1' as name,'m4360' as tp,'台' as unit,1 as num,'2222' as memo,'poreq000022' as fbillno,'' as [001],'' as [001供应商],'' as [001中标],6 as 李四,'北京蓝宝集团' as 李四供应商,'2.0送样中' as 李四中标
)
select @arg = (select * from a for xml raw,type,root('r'))
exec @prepare = sp_xml_preparedocument @handle output,@arg
insert into #con(rowid,colname,val)
--select a.localname,b.text from openxml(@handle,'/r',1) a left join openxml(@handle,'/r',1) b on b.parentid=a.id where a.parentid=0 order by a.id
select 1,a.localname,b.text from openxml(@handle,'/r',1) a left join openxml(@handle,'/r',1) b on b.parentid=a.id where a.nodetype=2 and datalength(b.text)>0
--select * from #con
--select * from tempdb..sysobjects where id=object_id('tempdb..#con')
declare @sql nvarchar(max)
set @sql = 'select * from (select rowid,colname,val from #con) a pivot(max(val) for colname in (' + (select stuff((select ',[' + colname + ']' from #con for xml path('')),1,1,'')) + ')) pvt'
exec(@sql)
drop table #con
针对单条数据只显示有有效数据的列