TSQL Challenge

On the series of TSQL Challenges, here is the next challenge. The requirement is to group the same parent when they have same child information.

Below is the sample source records where the expectation is to merge the Usage by Parent when they have same child information

Source Records

DECLARE @Source  table (SetID int, ParentName nvarchar(20), ParentGroup nvarchar(30),Usage int,ChildName nvarchar(20), Childgroup nvarchar(30))


--source information
insert into  @Source
select 1, 'Parent1', 'A', 10 , 'child1', 'B' union all
select 1, 'Parent1', 'A', 10 , 'child2', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child1', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child2', 'B' union all
select 3, 'Parent2', 'A', 10 ,'child1', 'B' union all
select 3, 'Parent2', 'A', 10 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child1', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child3', 'B' 

select * from @Source

Expected Records

Query 1

--source information
insert into  @Source
select 1, 'Parent1', 'A', 10 , 'child1', 'B' union all
select 1, 'Parent1', 'A', 10 , 'child2', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child1', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child2', 'B' union all
select 3, 'Parent2', 'A', 10 ,'child1', 'B' union all
select 3, 'Parent2', 'A', 10 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child1', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child3', 'B' 



--Expected output
DECLARE @Expected  table (ParentName nvarchar(20), ParentGroup nvarchar(30),Usage int)

insert into  @Expected
select 'Parent1', 'A', 25  union all
select 'Parent2', 'A', 10  union all
select 'Parent2', 'A', 20 
 ;with mycte as (
SELECT t1.SetID,t1.ParentName,t1.ParentGroup,t1.Usage,
       Stuff(( SELECT ',' + ChildName
           FROM @Source t2
          WHERE  t2.SetID= t1.SetID and t2.ParentName = t1.ParentName and  t2.ParentGroup = t1.ParentGroup
           order by t2.ChildName
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS combinedChildname
  FROM @Source t1
 GROUP BY  t1.SetID,t1.ParentName,t1.ParentGroup,t1.Usage)

 Select  ParentName, ParentGroup, SUM(Usage) Usage
 from mycte
 group by  ParentName, ParentGroup,combinedChildname

 

select * from @Expected

Query 2

DECLARE @Source  table (SetID int, ParentName nvarchar(20), ParentGroup nvarchar(30),Usage int,ChildName nvarchar(20), Childgroup nvarchar(30))


--source information
insert into  @Source
select 1, 'Parent1', 'A', 10 , 'child1', 'B' union all
select 1, 'Parent1', 'A', 10 , 'child2', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child1', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child2', 'B' union all
select 3, 'Parent2', 'A', 10 ,'child1', 'B' union all
select 3, 'Parent2', 'A', 10 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child1', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child3', 'B' 


 --------starting with SQL Server  2017
; with cte as (
select SetID,ParentName,ParentGroup,Usage,STRING_AGG (ChildName,',') as combinedChildname
from @Source
group by  SetID,ParentName,ParentGroup,Usage)
Select  ParentName, ParentGroup, SUM(Usage) Usage
from cte
group by  ParentName, ParentGroup,combinedChildname
This entry was posted in T-SQL and tagged , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *