22,210
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[tb_question]
if object_id('[tb_question]') is not null
drop table [tb_question]
go
create table [tb_question](
[dm] varchar(4),
[mc] varchar(6)
)
insert [tb_question]
select '0001','问题一' union all
select '0002','问题二' union all
select '0003','问题三' union all
select '0004','问题四' union all
select '0005','问题五'
go
--> 测试数据:[tb_poce]
if object_id('[tb_poce]') is not null
drop table [tb_poce]
go
create table [tb_poce](
[billcode] int,
[quest_dm] varchar(4),
[quest_value] varchar(4)
)
insert [tb_poce]
select 1,'0001','你' union all
select 1,'0002','好' union all
select 2,'0001','吗' union all
select 2,'0004','很好'
go
;with t
as(
select
*
from
[tb_question]
cross join
(
select
distinct [billcode]
from
[tb_poce]
) b
)
select
a.mc,
a.billcode,
b.quest_value
into
#tb
from
t a
left join
[tb_poce] b
on
a.billcode=b.billcode
and a.dm=b.quest_dm
go
declare @str varchar(2000)
set @str=''
select
@str=@str+','+mc+'=max(case when mc='+
QUOTENAME(mc,'''')+' then quest_value else null end)'
from
#tb
group by
mc
exec('select billcode as 单据号'+@str+' from #tb group by billcode')
go
/*
单据号 问题一 问题二 问题三 问题四 问题五
----------------------------------------------------------------
1 你 好 NULL NULL NULL
2 吗 NULL NULL 很好 NULL
*/