SELECT  
	people_code_id,
	CASE WHEN RECORD_NUM = 1 THEN NAME ELSE NULL END AS CHILD1,
	CASE WHEN RECORD_NUM = 2 THEN NAME ELSE NULL END AS CHILD2
FROM    RELATIONSHIP

SELECT  
	people_code_id,
	max(CASE WHEN RECORD_NUM = 1 THEN NAME ELSE NULL END) AS CHILD1,
	max(CASE WHEN RECORD_NUM = 2 THEN NAME ELSE NULL END) AS CHILD2
FROM    RELATIONSHIP
GROUP BY people_code_id

DECLARE @Cmd VARCHAR(MAX);

SELECT @Cmd = 'select ' + STUFF((SELECT ',(select [name] '
+ ' from RELATIONSHIP where Record_Num = ' + CAST(Record_Num AS VARCHAR(10)) + ') as Child' + CAST(ROW_NUMBER() OVER (PARTITION BY People_Code_ID ORDER BY Record_Num) AS VARCHAR(10))
FROM RELATIONSHIP
ORDER BY Record_Num
FOR XML PATH(''), TYPE).value('./text()[1]','varchar(max)'), 1,1,'');

EXEC (@Cmd);

--an exampl
--①create test table
create table test(indust varchar(10)
	,[200301] varchar(10)
	,[200302] varchar(10)
	,[200303] varchar(10))
insert test select 'a','111','222','333'
union all select 'b','444','555','666' 
union all select 'c','777','888','999'
union all select 'd','789','910','012'
go

--②table information
select  * from test
/*
indust     200301     200302     200303
---------- ---------- ---------- ----------
a          111        222        333
b          444        555        666
c          777        888        999
d          789        910        012

(4 affected)
*/

--③process
declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000)
select @f1='',@f2='',@f3=''
select @f1=@f1+',['+indust+']='''+[200301]+''''
	,@f2=@f2+','''+[200302]+''''
	,@f3=@f3+','''+[200303]+''''
from test
exec('select 日期=''200301'''+@f1
	+' union all select ''200302'''+@f2
	+' union all select ''200303'''+@f3)
go

/*--result
日期     a    b    c    d    
------ ---- ---- ---- ---- 
200301 111  444  777  789
200302 222  555  888  910
200303 333  666  999  012

(3 affected)
--*/

--④drop test table
drop table test