问题描述:
现在有两个表,tb_1和tb_2tb_1如下:
ID StrucType
1 单板
2 Whippletb_2如下:
LID LName LValue
1 靶板 1.5
2 缓冲屏 2.2
2 靶板 1.8 现在,我想得到结构为Whipple的详细信息,并且得到如下情况:
ID StrucType LName1 LValue1 LName2 LValue2
2 Whipple 缓冲屏 2.2 靶板 1.8
现在有两个表,tb_1和tb_2tb_1如下:
ID StrucType
1 单板
2 Whippletb_2如下:
LID LName LValue
1 靶板 1.5
2 缓冲屏 2.2
2 靶板 1.8 现在,我想得到结构为Whipple的详细信息,并且得到如下情况:
ID StrucType LName1 LValue1 LName2 LValue2
2 Whipple 缓冲屏 2.2 靶板 1.8
--select * from #tmp
select
id,structype,
[LName1]=max(case when [mid]='1' then [Lname] else null end),
[LValue1]=max(case when [mid]='1' then [LValue] else null end),
[LName2]=max(case when [mid]='2' then [Lname] else null end),
[LValue2]=max(case when [mid]='2' then [LValue] else null end)
from
#tmp
group by id,structypeGOdrop table #tmp/*
id structype LName1 LValue1 LName2 LValue2
----------- ---------- ---------- ---------- ---------- ----------
2 Whipple 缓冲屏 2.2 靶板 1.8
*/
--select * from #tmp
select
id,structype,
[LName1]=max(case when [mid]='1' then [Lname] else null end),
[LValue1]=max(case when [mid]='1' then [LValue] else null end),
[LName2]=max(case when [mid]='2' then [Lname] else null end),
[LValue2]=max(case when [mid]='2' then [LValue] else null end)
from
#tmp
group by id,structypeGOdrop table #tmp/*
id structype LName1 LValue1 LName2 LValue2
----------- ---------- ---------- ---------- ---------- ----------
2 Whipple 缓冲屏 2.2 靶板 1.8
*/