Query help with repeating parent id

Question

I'm trying to get the output in certain format for SSRS (eventual); I'm not sure if it's achievable through query.
This is for SQL2012; but SQL Fiddle only has SQL2017. Disclaimer- I'm just a low level developer and not up to me to upgrade the SQL Server.

Please view the following http://sqlfiddle.com/#!18/27daf
As you will see the 1st and 2nd line of the output because there are 2 child records. When I add to SSRS table, it repeats Parent and shows $100 twice.

Is there a write the query to present it:
ABCD0001 $100.00
EFGH0002 $100.00
IJKL00001 $0.00
ABCD0002 $30.00
EFGH0001 $150.00

In my mind, I guess I'll have to make the columns to rows; then row partition at parent id?
Any suggestions are welcome.

Thank you

Answer 1

Hi @J Like MIB ,

Or maybe you want this:

;with cte1 as (select p1.id1+p1.id2 as "ParentID", de1.balanceAmt as "ParentBalance",  
p2.id1+p2.id2 as "ChildID" ,de2.balanceAmt as "ChildBalance"  
from parentTbl p1  
left join crossTbl cr  
on p1.id1 = cr.pid1  
and p1.id2 = cr.pid2  
left join parentTbl p2  
on cr.cid1 = p2.id1  
and cr.cid2 = p2.id2  
left join detailTbl de1  
on p1.id1 = de1.id1  
and p1.id2 = de1.id2  
left join detailTbl de2  
on p2.id1 = de2.id1  
and p2.id2 = de2.id2  
)  
,cte2 as (  
select *,dense_rank()over(order by ParentID) as part from cte1 where ChildID is not null and ChildBalance is not null  
)  
,cte3 as(  
select *  from cte2  cross apply ( values   
     ( 'ParentID', 'ParentBalance'),  
	 ('ChildID','ChildBalance')  
 ) c([ParentID/ChildID], [ParentBalance/ChildBalance])  
 )  
,cte4 as(   
 select  [ParentID/ChildID],[ParentBalance/ChildBalance],n,[1],[2],part from cte3 cross apply (values(ParentID,ParentBalance,1),  
	 (ChildID,ChildBalance,2))d([1],[2],n)  
)  
,cte5 as(  
select distinct  [ParentID/ChildID],[ParentBalance/ChildBalance] ,[1],[2],part  
from cte4 where ([ParentID/ChildID]='ParentID' and n=1) or ([ParentID/ChildID]='ChildID' and n=2 )  
)select [ParentID/ChildID],[ParentBalance/ChildBalance],[1] AS [ParentID/ChildID2] ,[2] AS [ParentID/ChildID2]from cte5 order by part,[ParentID/ChildID] desc  

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @J Like MIB ,

Try this query:

;with cte1 as   
(select p1.id1+p1.id2 as "ParentID", de1.balanceAmt as "ParentBalance",  
p2.id1+p2.id2 as "ChildID" ,de2.balanceAmt as "ChildBalance"  
from parentTbl p1  
left join crossTbl cr  
on p1.id1 = cr.pid1  
and p1.id2 = cr.pid2  
left join parentTbl p2  
on cr.cid1 = p2.id1  
and cr.cid2 = p2.id2  
left join detailTbl de1  
on p1.id1 = de1.id1  
and p1.id2 = de1.id2  
left join detailTbl de2  
on p2.id1 = de2.id1  
and p2.id2 = de2.id2  
)  
,cte2 as (  
select *,dense_rank()over(order by ParentID) as part  
from cte1 where ChildID is not null and ChildBalance is not null  
)  
,cte3 as(  
select ParentID as [ParentID/ChildID],ParentBalance as [ParentBalance/ChildBalance],part  
from cte2  
union  
select ChildID,ChildBalance,part  
from cte2  
  
   
  
)  
select [ParentID/ChildID],[ParentBalance/ChildBalance]   
from cte3   
order by part  

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.