add-empty-rows-after-certain-records-in-a-table, Sql Server

add-empty-rows-after-certain-records-in-a-table

declare @tbl table
(
	[Desc] varchar(10),
	[ParentID] varchar(10),
	[Child1ID] varchar(10),
	[Child2ID] varchar(10)
)
insert @tbl values
	('Parent', 'PID1', null, null),
	('Child', 'PID1', 'CID1', null),
	('Child2', 'PID1', 'CID1', 'C2ID1'),
	('Child', 'PID1', 'CID2', null),
	('Parent', 'PID2', null, null)

--select * from @tbl order by ParentID, Child1ID, Child2ID

;
with
t as
(
	select 
		row_number() over (order by ParentID, Child1ID, Child2ID) as RId,
		row_number() over (partition by ParentID order by ParentID, Child1ID, Child2ID) as RPId,
		ParentID, 
		Child1ID, 
		Child2ID
	from 
		@tbl
),
tm as
(
	select 
		ParentID, 
		max(RId) as max_RId 
	from 
		t
	group by 
		ParentID
),
u as
(
	select
		RId * 10 as RId,
		RPId,
		ParentID,
		Child1ID,
		Child2ID
	from
		t

union all

-- after last child
	select
		max_RId * 10 + 5,
		null, null, null, null
	from
		tm

union all

-- before first parent
	select
		5,
		null, null, null, null
)
select
	ParentID,
	Child1ID,
	Child2ID
from
	u
order by 
	RId


run \| edit \| history \| help	0