SQL Recursive CTE Amendment

Question

I will appreciate the community assistance in amending this Recursive CTE below. My Schema and Expected output are included.

 WITH cte AS(  
 SELECT  
     us.HeaderDescription,  
     up.ScheduleName,  
     up.HeaderCode,  
     up.PolicyRefCode  
 FROM  
     Underwriting_Policy_Schedule_Header_Tittle us  
     JOIN Underwriting_Policy_Schedule_Header up  
     ON us.HeaderCode=up.HeaderCode  
     )  
     ,cte2 AS (SELECT c.ScheduleName,  
                c.HeaderCode,  
                c.HeaderDescription,  
                c.PolicyRefCode,  
                ups.ItemNo,  
                ups.Description,  
                ups.SumInsured,  
                ups.PremiumRate,  
                ups.PremiumAmount,  
                ups.NetPremiumAmount  
     FROM cte c  
     JOIN Underwriting_Policy_Schedule ups  
     ON c.HeaderCode=ups.HeaderCode AND ups.PolicyRefCode='0000000934')  
          
      
     ,cte3 AS (SELECT  DISTINCT ScheduleName,  
                                HeaderCode,  
                                PolicyRefCode,  
                                NULL ItemNo,   
                                HeaderDescription,  
                                NULL SumInsured,   
                                NULL PremiumRate,  
                                NULL PremiumAmount,  
                                NULL NetPremiumAmount  
     FROM cte2   
          
     UNION ALL  
      
     SELECT  ScheduleName,  
             HeaderCode,  
             PolicyRefCode,  
             ItemNo,  
             Description,  
             SumInsured,  
             PremiumRate,  
             PremiumAmount,  
             NetPremiumAmount  
     FROM cte2)  
      
     SELECT * FROM cte3 WHERE PolicyRefCode='0000000934'  
 ORDER BY ScheduleName, HeaderCode, ItemNo  

The CTE Query above is working but I needed to amends it such that more columns are added from Underwriting_Policy_Schedule_Header to appear under HeaderDescription column.

Below is the schema.

 CREATE TABLE [dbo].[Underwriting_Policy_Schedule_Header_Tittle](  
  [HeaderCode] [char](4)  NOT NULL PRimary key,  
  [HeaderDescription] [varchar](200) NULL,  
  [HeaderShortDescription] [varchar](70) NULL,  
      
 )  
     
 INSERT INTO [dbo].[Underwriting_Policy_Schedule_Header_Tittle]  
        ([HeaderCode]  
        ,[HeaderDescription]  
        ,[HeaderShortDescription])  
  VALUES  
      ('0004','PLANT ALL RISKS''PAR'),  
      ('0005','MATERIAL DAMAGE''MAT DMG'),  
     ('0006','PUBLIC LIABILITY''PUB. LIAB.'),  
     ('0007','ELECTRONIC EQUIPMENT INSURANCE''ELECT. EQUIP. INS.')  
      
 CREATE TABLE [dbo].[Underwriting_Policy_Schedule_Header](  
  [RecordID] [int] IDENTITY(1,1) NOT NULL, -- PK  
  [PolicyRefCode] [char](10) NOT NULL,  -- FK  
  [HeaderCode] [char](4) NULL, -- FK  
  [ScheduleName] [varchar](150) NULL,  
  [ScopeofCover] [varchar](2048) NULL,  
  [CoverExtension] [varchar](2048) NULL,  
  [CoverExclusion] [varchar](2048) NULL,  
  [SubjectMatter] [varchar](2048) NULL,  
  [Remark] [varchar](2048) NULL,  
  [Excess] [varchar](2048) NULL,  
  [Warranty] [varchar](2048) NULL,  
  [Benefit] [varchar](2048) NULL,	  
  [IndemnityPeriod] [varchar](2048) NULL,  
  [TermAndCondition] [varchar](2048) NULL,  
 )   
      
 INSERT INTO [dbo].[Underwriting_Policy_Schedule_Header]  
     ([RecordID]  
     ,[PolicyRefCode]  
     ,[HeaderCode]  
     ,[ScheduleName]  
     ,[ScopeofCover]       
     ,[CoverExtension]  
     ,[CoverExclusion]  
     ,[SubjectMatter]  
     ,[Remark]  
     ,[Excess]  
     ,[Warranty]  
     ,[Benefit]  
     ,[IndemnityPeriod]  
     ,[TermAndCondition])  
  VALUES  
      (13202,'0000000934''0004''SCHEDULE A:''The policy will provide indemnity for all risks of physical loss or damage.' 'Cover Extension' 'Cover Exclusion' 'Subject Matter' 'Remark' 'Excess' 'Warranty' 'Benefit' 'Indemnity Period' 'Terms and Condition'),  
      (13203,'0000000934''0005''SCHEDULE B:''The policy will indemnify you in respect of any loss arising from claims for breach of duty.' 'Cover Extension' 'Cover Exclusion' 'Subject Matter' 'Remark' 'Excess' 'Warranty' 'Benefit' 'Indemnity Period' 'Terms and Condition'),  
     (13204,'0000000934''0006''SCHEDULE C:''The policy will indemnify you in respect of any loss arising from claims for breach of duty.' 'Cover Extension' 'Cover Exclusion' 'Subject Matter' 'Remark' 'Excess' 'Warranty' 'Benefit' 'Indemnity Period' 'Terms and Condition'),  
     (13205,'0000000934''0007''SCHEDULE D:''The policy will provide indemnity for all risks of physical loss or damage.' 'Cover Extension' 'Cover Exclusion' 'Subject Matter' 'Remark' 'Excess' 'Warranty' 'Benefit' 'Indemnity Period' 'Terms and Condition'),  
      
      
 CREATE TABLE [dbo].[Underwriting_Policy_Schedule](  
  [RecordID] [int] IDENTITY(1,1) NOT NULL, PK  
  [ItemNo] [char](10) NULL,  
  [PolicyRefCode] [char](10) NOT NULL,   FK  
  [HeaderCode] [char](4) NULL, FK  
  [ScheduleName] [char](1) NULL,  
  [Description] [varchar](2048) NULL,  
  [SumInsured] [decimal](19, 4) NULL,  
  [PremiumRate] [decimal](10, 7) NULL,  
  [PremiumAmount] [decimal](19, 4) NULL,  
  [NetPremiumAmount] [decimal](19, 4) NULL,  
    
 )  
      
 INSERT INTO [dbo].Underwriting_Policy_Schedule  
   ([ItemNo]  
  ,[PolicyRefCode]    
  ,[HeaderCode]  
  ,[ScheduleName]  
  ,[Description]  
  ,[SumInsured]  
  ,[PremiumRate]  
  ,[PremiumAmount]  
  ,[NetPremiumAmount])  
  VALUES  
      ('1','0000000934''0004''SCHEDULE A:''Generators',37221450,1.5,558321.75,558321.75),  
     ('2','0000000934''0004''SCHEDULE A:''Sewage Treatment Plant',14987000.00,0.2,3435069.58,3435069.58),  
     ('3','0000000934''0004''SCHEDULE A:''Transformer( 300 KVA)',10532200.00,1.8,189579.60,189579.60),  
     ('4','0000000934''0004''SCHEDULE A:''Elevator',39900000.00,1.2,478800.00, 478800.00),  
      
     ('1','0000000934''0005''SCHEDULE B:''On main hotel building of 32 bedrooms including but not limited to all outbuildings, water tanks,boreholes,pumps,diesel tank, landlords fixtures and fiittings situate 18, Obafemi Anibaba, off Admirality Road beside FoodcoLekki Lagos.',460700953.15,0.2,921401.91, 921401.91),  
     ('2','0000000934''0005''SCHEDULE B:''On complete boundary walls, gates and fences including electric fence',200000,0.2,40000.58,40000.58),  
     ('3','0000000934''0005''SCHEDULE B:''On all items of any description including but not limited to Furniture, Furnishings, decorative Light fittings, Kitchen equipment of various description,Coldroom,refrigerators, Chandeliers Air Conditioning,Painting and Artworks,Water heater,Bed and Mattresses, Personal effect of Guest, used and unused Stationery, HVAC/Dry contact and all other contents contained in the hotel',581001992.87,0.9,522901.74,522901.74),  
      
     ('1','0000000934''0006''SCHEDULE C:''Limit of Indemnity',25000000,0.1,25000.00,25000.00),  
     ('2','0000000934''0006''SCHEDULE C:''Limit ro Third Party Property Damage',10000000,0.1,10000.00,10000.00),  
         
     ('1','0000000934''0007''SCHEDULE D:''On electronic equipments of all description including but not limited to Television ',63239115.80,0.5,318195.58,318195.58),  
     ('2','0000000934''0007''SCHEDULE D:''On Computer Related devices',200000,0.2,40000.58,40000.58),  
     ('3','0000000934''0007''SCHEDULE D:''On Electronic Apllicances',10279786,0.5,51398.93,51398.93)  

Expected output result is attached below

@EchoLiu-MSFT
Your comment, advise and amendment will be greatly appreciated.

Best regards
Lawrence

Answer 1

Actually, that is not a recursive CTE.

Thanks for providing CREATE TABLE + INSERT. However, I would encourage you to test the script next time. I had to clean up a couple of syntax errors.

Anyway, here is a solution:

WITH cte AS(
      SELECT  us.HeaderDescription, up.ScheduleName, up.HeaderCode, up.PolicyRefCode, 
              up.CoverExtension, up.CoverExclusion, up.SubjectMatter, up.Remark, 
              up.Excess, up.Warranty, up.Benefit, up.IndemnityPeriod, up.TermAndCondition
      FROM    Underwriting_Policy_Schedule_Header_Tittle us
      JOIN Underwriting_Policy_Schedule_Header up ON us.HeaderCode=up.HeaderCode
) , cte2 AS (
      SELECT c.ScheduleName, c.HeaderCode, c.HeaderDescription, c.PolicyRefCode, ups.ItemNo,
             ups.Description, ups.SumInsured, ups.PremiumRate, ups.PremiumAmount, ups.NetPremiumAmount,
             c.CoverExtension, c.CoverExclusion, c.SubjectMatter, c.Remark, 
             c.Excess, c.Warranty, c.Benefit, c.IndemnityPeriod, c.TermAndCondition
      FROM cte c
      JOIN Underwriting_Policy_Schedule ups ON c.HeaderCode=ups.HeaderCode AND ups.PolicyRefCode='0000000934'
), cte3 AS (
   SELECT  DISTINCT c.ScheduleName, c.HeaderCode, c.PolicyRefCode, NULL AS ItemNo, V.Description, V.Sorter, 
                    NULL AS SumInsured, NULL AS PremiumRate, NULL AS PremiumAmount, NULL AS NetPremiumAmount
   FROM    cte2 c
   CROSS APPLY (VALUES(0, HeaderDescription), 
                       (1, 'The policy will indeminify...'),  
                       (2, CoverExtension), (3, CoverExclusion), (4, SubjectMatter), 
                       (5, Remark), (6, Excess), (7, Warranty), (8, Benefit), 
                       (9, IndemnityPeriod), (10, TermAndCondition)) AS V(Sorter, Description)
   UNION ALL
   SELECT  ScheduleName, HeaderCode, PolicyRefCode, ItemNo, Description, NULL,
           SumInsured, PremiumRate, PremiumAmount,NetPremiumAmount
   FROM cte2
)
SELECT ScheduleName, HeaderCode, PolicyRefCode, ItemNo, Description, 
       SumInsured, PremiumRate, PremiumAmount,NetPremiumAmount 
FROM cte3 
WHERE  PolicyRefCode='0000000934'
ORDER BY ScheduleName, HeaderCode, ItemNo, Sorter

Answer 2

Hi @lawrence ,

Recursive cte is suitable for parent-child structure data or tree structure data. Your data does not need to use recursive cte.

You can use UNPIVOT to convert the rows in [dbo].[Underwriting_Policy_Schedule_Header] into columns.

Please also check:

;WITH cte AS(  
   SELECT us.HeaderDescription,up.ScheduleName,  
      up.HeaderCode,up.PolicyRefCode  
   FROM Underwriting_Policy_Schedule_Header_Tittle us  
   JOIN Underwriting_Policy_Schedule_Header up  
   ON us.HeaderCode=up.HeaderCode)  
,cte2 AS   
  (SELECT c.ScheduleName,c.HeaderCode,c.HeaderDescription,  
    c.PolicyRefCode,ups.ItemNo,ups.Description,  
    ups.SumInsured,ups.PremiumRate,ups.PremiumAmount,ups.NetPremiumAmount  
   FROM cte c  
   JOIN Underwriting_Policy_Schedule ups  
   ON c.HeaderCode=ups.HeaderCode AND ups.PolicyRefCode='0000000934')  
,cte3 AS  
  (SELECT * FROM (SELECT *  
   FROM [dbo].[Underwriting_Policy_Schedule_Header]) as t  
   UNPIVOT (HeaderDescription FOR  Columname IN([ScopeofCover],[CoverExtension]  
           ,[CoverExclusion],[SubjectMatter],[Remark],[Excess],[Warranty],[Benefit]  
           ,[IndemnityPeriod],[TermAndCondition])) as up)  
,cte4 AS   
  (SELECT  DISTINCT ScheduleName,  
   HeaderCode,PolicyRefCode,ItemNo, Description,  
  SumInsured,PremiumRate,PremiumAmount, NetPremiumAmount  
   FROM cte2   
   UNION ALL  
   SELECT ScheduleName,HeaderCode,PolicyRefCode,NULL ItemNo, HeaderDescription,  
   NULL SumInsured,NULL PremiumRate,NULL PremiumAmount, NULL NetPremiumAmount  
   FROM cte3)  
          
SELECT * FROM cte4   
WHERE PolicyRefCode='0000000934'  
ORDER BY ScheduleName, HeaderCode, ItemNo  

Output:

For more detail,please refer to:
FROM - Using PIVOT and UNPIVOT

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.