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.

here is the example 99999999 has 2 dependents 22222222 and 33333333.

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.

    de.ID as ConsumerID
    ,dd.ID as DependentID,
    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
  • Please add your table schema. – McNets Jun 7 '17 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.