我在SQL Server 2017中有一个表,如下所示:
Type Description
H General Income
H Property Income
R Rent
R Parking
R Storage
H Cash Flow
H Other Income
R Specials
这里,H表示Header
,R表示Role
。这些角色属于在其上方定义的标头。例如,General Income
还没有定义角色,但Property Income
下面有三个角色——rent
、parking
和storage
。
我的预期输出是:
Header RoleDescription
General Income
Property Income Rent
Property Income Parking
Property Income Storage
Cash Flow
Other Income Specials
我怎么能得到这个,我找不到。如果有任何帮助,我将不胜感激。
编辑:我有一个ID栏,是的,我相信我可以用它来订购。
假设您的表有一个排序列,您可以通过使用累积条件和来定义组来实现这一点。
然后有一个技巧。对角色使用条件聚合,对标头使用带窗口函数的条件聚合:
select max(max(case when type = 'H' then description end)) over (partition by grp) as header,
max(case when type = 'R' then description end) as role
from (select t.*,
row_number() over (partition by type, grp order by <ordering col) as seqnum
from (select t.*,
sum(case when type = 'H' then 1 else 0 end) over (order by <ordering col>) as grp
from t
) t
) t
group by seqnum
order by grp, seqnum;