27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([fcode] nvarchar(22),[comm] nvarchar(21))
Insert #T
select N'01',N'A' union all
select N'01',N'B' union all
select N'02',N'1' union all
select N'02',N'3'
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY fcode ORDER BY comm DESC) rn from #T
)
select fcode'
;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY fcode ORDER BY comm DESC) rn from #T
)
SELECT @sql = @sql + ',max(case rn when ' + RTRIM(rn)
+ ' then comm else null end)[comm' + RTRIM(rn) + ']'
FROM ( SELECT DISTINCT
rn
FROM cte
) a
SET @sql = @sql
+ ' from cte group by fcode;'
EXEC(@sql)