表 rela 结构如下
reid pid attrid attname attrvalid attname length
1 1 1 口径 1 DN50 200
2 1 2 压力等级 11 PN10bar 300
3 2 1 口径 1 DN60 200
4 2 2 压力等级 12 PN16bar 300
5 2 3 最小流量 57 8 400
现在要结果如下
pid 口径 压力等级 最小流量-400
1 DN50 PN10bar --
2 DN60 PN16bar 8
------解决思路----------------------
select
T1.pid,
ISNULL(T2.attname, '--') as [口径],
ISNULL(T3.attname, '--') as [压力等级],
ISNULL(T4.attname, '--') as [最小流量-400]
from rela as T1
left join
(
select pid, attname
from rela
where attname = '口径'
) as T2
ON T1.pid = T2.pid
left join
(
select pid, attname
from rela
where attname = '压力等级'
) as T3
ON T1.pid = T3.pid
left join
(
select pid, attname
from rela
where attname = '最小流量'
) as T4
ON T1.pid = T4.pid
------解决思路----------------------
你的最小流量的列名是要加上length的值吗 是的话就像这样做，不是的话把replace函数去掉就好
select * into #t from
(
select 1 as pid,'口径' as attname,'DN50' as attclass,200 as [length] union all
select 1,'压力等级','PN10bar',300 union all
select 2,'口径','DN60',200 union all
select 2,'压力等级','PN16bar',300 union all
select 2,'最小流量','8',400
) a
declare @cc nvarchar(100)
declare @s nvarchar(max)
select @cc=ISNULL(@cc+',','')+'['+replace(attname,'最小流量',attname+'-'+convert(nvarchar(50),length))+']' from #t group by attname,length
set @s='
select * from
(
select pid,replace(attname,''最小流量'',attname+''-''+convert(nvarchar(20),length))attname,attclass from #t
) p
pivot (min(attclass) for attname in('+@cc+')) t
'
print @s
exec (@s)
drop table #t
------解决思路----------------------
select * into #t from
(
select 1 as pid,'口径' as attname,'DN50' as attclass,200 as [length] union all
select 1,'压力等级','PN10bar',300 union all
select 2,'口径','DN60',200 union all
select 2,'压力等级','PN16bar',300 union all
select 2,'最小流量','8',400
) a
select * from #t
declare @s varchar(max)
select @s=ISNULL(@s+',','')+'MIN(case when attname='''+attname+''' then attclass end) as ['+replace(attname,'最小流量',attname+'-'+convert(varchar(50),length))+']'
from #T group by attname,length
set @s='select pid,'+@s+' from #t group by pid'
print @s
exec(@s)
drop table #t