--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([ID] int,[name] nvarchar(23))
Insert #A
select 1,N'刘德华' union all
select 2,N'张杰' union all
select 3,N'林俊杰'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([ID] int,[AID] int,[SNAME] nvarchar(27),[Stime] nvarchar(27))
Insert #B
select 1,1,N'冰雨',N'1998-10' union all
select 2,1,N'爱你一万年',N'2000-08' union all
select 3,2,N'三生三世',N'2017-03' union all
select 4,1,N'我恨我痴心',N'1996-10' union all
select 5,2,N'这就是爱',N'2011-05' union all
select 6,3,N'小酒窝',N'2008-09' union all
select 7,2,N'逆战',N'2012-01' union all
select 8,3,N'醉赤壁',N'2008-10' union all
select 9,3,N'江南',N'2004-06' union all
select 10,2,N'给女儿的一封信',N'2018-05'
Go
--测试数据结束
DECLARE @sql VARCHAR(8000)
SET @sql = ';WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY AID ORDER BY Stime)rn from #B
)
select AId,#'
;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY AID ORDER BY Stime)rn from #B
)
SELECT @sql = @sql + ',max(case rn when ' + RTRIM(rn)
+ ' then SNAME else null end)[SNAME' + RTRIM(rn) + ']'+ ',max(case rn when ' + RTRIM(rn)
+ ' then Stime else null end)[Stime' + RTRIM(rn) + ']'
FROM ( SELECT DISTINCT
FROM cte
) a
SET @sql = @sql
+ ' from cte join #A on # = Aid group by AId,# order by AId'
EXEC(@sql)