请问专家，如何实现左表转换为右表？十分感谢！
------解决思路----------------------
可以試一下
declare @tbl table
(
projectNo varchar(20),
projectName nvarchar(20),
isDone char(1),
finishDate datetime,
subjectName nvarchar(10),
sell decimal(13,2),
buy decimal(13,2)
)
insert @tbl
select '02','春光小區','0',null,'期初',null,null union all
select '02','春光小區','0',null,'借方',null,null union all
select '02','春光小區','0',null,'貨方',null,null union all
select '02','春光小區','0',null,'期末',null,null union all
select '02001','30#','0',null,'期初',null,null union all
select '02001','30#','0',null,'借方',null,null union all
select '02001','30#','0',null,'貨方',null,null union all
select '02001','30#','0',null,'期末',null,null union all
select '02001001','1單元4層402室','0',null,'期初',null,null union all
select '02001001','1單元4層402室','0',null,'借方',null,6451.00 union all
select '02001001','1單元4層402室','0',null,'貨方',1212.00,null union all
select '02001001','1單元4層402室','0',null,'期末',-1212.00,6451.00 union all
select '02001004','2單元1層103室','0',null,'期初',null,null union all
select '02001004','2單元1層103室','0',null,'借方',null,8039.00 union all
select '02001004','2單元1層103室','0',null,'貨方',null,null union all
select '02001004','2單元1層103室','0',null,'期末',null,8039.00 union all
select '02002','36#','0',null,'期初',null,null union all
select '02002','36#','0',null,'借方',null,null union all
select '02002','36#','0',null,'貨方',18280.00,null union all
select '02002','36#','0',null,'期末',-18280.00,null union all
select '02002001','1單元1層102室','0',null,'期初',null,null union all
select '02002001','1單元1層102室','0',null,'借方',null,null union all
select '02002001','1單元1層102室','0',null,'貨方',18280.00,null union all
select '02002001','1單元1層102室','0',null,'期末',-18280.00,null union all
select '02003','19#','0',null,'期初',null,null union all
select '02003','19#','0',null,'借方',null,null union all
select '02003','19#','0',null,'貨方',15379.00,null union all
select '02003','19#','0',null,'期末',-15379.00,null union all
select '02003001','5單元1層101室','0',null,'期初',null,null union all
select '02003001','5單元1層101室','0',null,'借方',null,null union all
select '02003001','5單元1層101室','0',null,'貨方',15379.00,null union all
select '02003001','5單元1層101室','0',null,'期末',-15379.00,null ;
--select * from @tbl;
with cte as
(
select *,1 as lev from @tbl where projectNo='02'
union all
select a.projectNo,cast(b.projectName+a.projectName as nvarchar(20)), a.isDone,a.finishDate,a.subjectName,a.sell,a.buy,lev+1
from @tbl as a,cte as b where substring(a.projectNo,1,len(a.projectNo)-3)=b.projectNo and a.projectNo!='02'
)
select distinct projectNo,projectName,isDone,finishDate,subjectName,sell,buy
from cte where lev=(select MAX(lev) from cte) and subjectName='期末'
------解决思路----------------------
再贴一次~~
如果你的真实数据和你贴出来的，在项目编号上的规则有较大出入，再跟贴说明下吧
SELECT
C.[项目编号]
,CAST(A.[项目名称]AS NVARCHAR(4000))+B.[项目名称]+C.[项目名称][项目名称]
,C.[完工][完工否]
,C.[完工日期]
,C.[卖出]
,C.[买入]
FROM
[数据表]A
LEFT JOIN[数据表]B ON A.[项目编号]=LEFT(B.[项目编号],LEN(A.[项目编号]))AND LEN(A.[项目编号])+3=LEN(B.[项目编号])AND B.[统计栏目]=N'期末'
LEFT JOIN[数据表]C ON B.[项目编号]=LEFT(C.[项目编号],LEN(B.[项目编号]))AND LEN(B.[项目编号])+3=LEN(C.[项目编号])AND C.[统计栏目]=N'期末'
WHERE
A.[项目编号]='02'AND A.[统计栏目]=N'期末'
ORDER BY
C.[项目编号]
--效果如下，以下不是语句
/*
02001001 暮光小区30#1单元4层402室 0 NULL -1212 6451
02001004 暮光小区30#2单元1层403室 0 NULL NULL 8039
02002001 暮光小区36#1单元1层402室 0 NULL -18280 NULL
02003001 暮光小区19#5单元1层401室 0 NULL -15379 NULL
*/