I want to have the data displayed in the below parent child like relationship, where the first row should be parent row, and every subsequent row should be for child with null in all parent specific columns.
In the above the consumer 99999999 has 2 dependents 22222222 and 33333333.
I tried doing this with Lead and lag but that will need an order by in the CTE. I was thinking of looking at the previous
ApplicationroductID, and would flag whenever I see that it has changed. So by doing that my I can flag the parent and Child. I could potentially get away with the order by problem by using a temp table, but that doesnt sound like a very good solution. Is there a better way of doing this that you guys can think of. I want to have the data to be in the above format.
select de.ID as ConsumerID ,dd.ID as DependentID, f.ApplicationroductID, LEAD(f.ApplicationroductID) OVER (Order by fep.ApplicationroductID) Lead, LAG(f.ApplicationroductID) OVER (Order by fep.ApplicationroductID) Lag from Fact f INNER JOIN DimEmp de ON fep.DimEmp_FK = de.ID INNER JOIN DimDep dd ON fep.DimDep_FK = dd.ID