convert dynamic rows to columns in sql server

• asrinu13

  Say Hey Kid

  Points: 709

  More actions

  March 27, 2020 at 1:44 pm

  #3738243

  Hi i have one doubt in sql server how to convert rows to columns dynamically in sql server

  CREATE TABLE [dbo].[product](

  [productid] [varchar](20) NULL,

  [productEventID] [varchar](50) NULL,

  [productTransID] [varchar](50) NULL,

  [productEffectiveDate] [datetime] NULL,

  [productInvestmentID] [varchar](20) NULL,

  [productBuySellCode] [varchar](10) NULL,

  [productClosingValue] [decimal](13, 2) NULL,

  [productClosingUnits] [int] NULL,

  [productChangeInUnit] [varchar](20) NULL,

  [productChangeinvalue] [varchar](20) NULL,

  [productNAVPriceDate] [datetime] NULL,

  [productNAVValue] [decimal](13, 2) NULL,

  [productAllocPercent] [decimal](13, 2) NULL,

  [productSurrCrdtHdr] [decimal](13, 2) NULL,

  [productSurrChrgHdr] [decimal](13, 2) NULL,

  [productBaseCOICharge] [decimal](13, 2) NULL,

  [productRiderCOICharge] [decimal](13, 2) NULL,

  [productFlatExtraCOICharge] [decimal](13, 2) NULL,

  [productAdminFeeChrg] [decimal](13, 2) NULL,

  [productAllocationCharge] [decimal](13, 2) NULL,

  [productUnitRecordType] [varchar](2) NULL,

  [productTopupLayerNo] [int] NULL,

  [productTrans_Fundcode] [varchar](10) NULL

  ) ON [PRIMARY]

  INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'101', CAST(N'2020-03-12T00:00:00.000' AS DateTime), N'Fund2', N'B', CAST(7202.00 AS Decimal(13, 2)), 150, N'150', N'4700.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(19.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'mobile', N'105', CAST(N'2020-03-14T00:00:00.000' AS DateTime), N'Fund4', N'S', CAST(19339.91 AS Decimal(13, 2)), 100, N'100', N'5000', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(17.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'107', CAST(N'2020-03-13T00:00:00.000' AS DateTime), N'Fund2', N'S', CAST(6802.96 AS Decimal(13, 2)), 150, N'400', N'100.043', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(19.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'100', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund2', N'B', CAST(7520.00 AS Decimal(13, 2)), 150, N'150', N'7520.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(19.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'102', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund3', N'S', CAST(8001.91 AS Decimal(13, 2)), 100, N'100', N'1000.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(17.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0005')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'100', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund1', N'B', CAST(3760.00 AS Decimal(13, 2)), 200, N'200', N'3760.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(18.50 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'101', CAST(N'2020-03-12T00:00:00.000' AS DateTime), N'Fund4', N'B', CAST(19739.00 AS Decimal(13, 2)), 150, N'150', N'4700.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(16.30 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'100', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund4', N'B', CAST(15040.00 AS Decimal(13, 2)), 150, N'150', N'15040.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(16.30 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'101', CAST(N'2020-03-12T00:00:00.000' AS DateTime), N'Fund1', N'B', CAST(8699.00 AS Decimal(13, 2)), 200, N'200', N'4700.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(18.50 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DateTime), N'Fund1', N'S', CAST(8244.00 AS Decimal(13, 2)), 200, N'', N'75', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(18.50 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'107', CAST(N'2020-03-13T00:00:00.000' AS DateTime), N'Fund1', N'S', CAST(8299.48 AS Decimal(13, 2)), 200, N'400', N'300.522', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(18.50 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'101', CAST(N'2020-03-12T00:00:00.000' AS DateTime), N'Fund3', N'B', CAST(12701.91 AS Decimal(13, 2)), 100, N'100', N'4700.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(17.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0003')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'102', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund1', N'S', CAST(3499.48 AS Decimal(13, 2)), 200, N'200', N'1', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(18.50 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'106', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund2', N'S', CAST(7000.96 AS Decimal(13, 2)), 150, N'150', N'519.043', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(19.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'mobile', N'104', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund4', N'S', CAST(9001.91 AS Decimal(13, 2)), 100, N'100', N'5000', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(17.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'106', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund4', N'S', CAST(14001.91 AS Decimal(13, 2)), 150, N'150', N'1038.086', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(16.30 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DateTime), N'Fund1', N'B', CAST(8434.00 AS Decimal(13, 2)), 200, N'200', N'190', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(18.50 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'102', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund2', N'B', CAST(2502.00 AS Decimal(13, 2)), 150, N'150', N'502', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(19.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0003')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DateTime), N'Fund2', N'B', CAST(6687.00 AS Decimal(13, 2)), 150, N'150', N'110', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(19.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0003')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'107', CAST(N'2020-03-13T00:00:00.000' AS DateTime), N'Fund4', N'S', CAST(19439.91 AS Decimal(13, 2)), 150, N'400', N'100.086', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(16.30 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'106', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund1', N'S', CAST(3500.48 AS Decimal(13, 2)), 200, N'200', N'259.522', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(18.50 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DateTime), N'Fund2', N'S', CAST(6577.00 AS Decimal(13, 2)), 150, N'150', N'75', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(19.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DateTime), N'Fund4', N'S', CAST(1924.00 AS Decimal(13, 2)), 150, N'150', N'75', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(16.30 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0006')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DateTime), N'Fund3', N'S', CAST(12226.00 AS Decimal(13, 2)), 100, N'100', N'75', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(17.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0005')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'102', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund4', N'S', CAST(15039.00 AS Decimal(13, 2)), 150, N'150', N'1.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(16.30 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0006')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'100', CAST(N'2020-02-19T00:00:00.000' AS DateTime), N'Fund3', N'B', CAST(11280.00 AS Decimal(13, 2)), 100, N'100', N'11280.0', CAST(N'2020-02-19T00:00:00.000' AS DateTime), CAST(17.60 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(0.00 AS Decimal(13, 2)), CAST(100.00 AS Decimal(13, 2)), N'N', 0, N'F0003')

  I tried dynamaic case statment.its not working as per the above data .

  query is :

  --dynamic with case

  DECLARE @query NVARCHAR(4000) =null

  declare @ColumnHeaders NVARCHAR(4000) ;

  set @ColumnHeaders = STUFF( (SELECT ','

  + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productinvestmentid else null end ) as ' + quotename('productinvestmentid'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productBuySellCode else null end ) as ' + quotename('productBuySellCode'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productEffectiveDate else null end ) as ' + quotename('productEffectiveDate'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13) +','

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productClosingValue else null end ) as ' + quotename('productClosingValue'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productAllocationCharge else null end ) as ' + quotename('productAllocationCharge'+Cast(rn as varchar(10)),'[') + char(10)+char(13) +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productClosingUnits else null end ) as ' + quotename('productClosingUnits'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productChangeInUnit else null end ) as ' + quotename('productChangeInUnit'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productChangeinvalue else null end ) as ' + quotename('productChangeinvalue'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productNAVPriceDate else null end ) as ' + quotename('productNAVPriceDate'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productNAVValue else null end ) as ' + quotename('productNAVValue'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productAllocPercent else null end ) as ' + quotename('productAllocPercent'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productSurrCrdtHdr else null end ) as ' + quotename('productSurrCrdtHdr'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productSurrChrgHdr else null end ) as ' + quotename('productSurrChrgHdr'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productBaseCOICharge else null end ) as ' + quotename('productBaseCOICharge'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13) +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productRiderCOICharge else null end ) as ' + quotename('productRiderCOICharge'+Cast(rn as varchar(10)),'[') + char(10)+char(13) +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productFlatExtraCOICharge else null end ) as ' + quotename('productFlatExtraCOICharge'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13) +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productAdminFeeChrg else null end ) as ' + quotename('productAdminFeeChrg'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13) +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productUnitRecordType else null end ) as ' + quotename('productUnitRecordType'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13) +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productTopupLayerNo else null end ) as ' + quotename('productTopupLayerNo'+Cast(rn as varchar(10)),'[') + char(10)+char(13)

  +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN productTrans_Fundcode else null end ) as ' + quotename('productTrans_Fundcode'+Cast(rn as varchar(10)),'[')

  + char(10)+char(13) FROM (

  select productid,producteventid,producttransid,productinvestmentid,productBuySellCode, productEffectiveDate,productClosingValue,productAllocationCharge,productClosingUnits,productChangeInUnit,productChangeinvalue,productNAVPriceDate,productNAVValue,productAllocPercent,productSurrCrdtHdr,productSurrChrgHdr,productBaseCOICharge,productRiderCOICharge,productFlatExtraCOICharge,productAdminFeeChrg,productUnitRecordType,productTopupLayerNo,productTrans_Fundcode, row_number() over(partition by productid,producteventid,producttransid order by productid,producteventid,producttransid) rn

  from product) t group by rn Order by rn FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');

  print @ColumnHeaders

  Set @query =N';with mycte as (

  select productid,producteventid,producttransid,productinvestmentid,productBuySellCode, productEffectiveDate

  ,productClosingValue,productAllocationCharge,productClosingUnits,productChangeInUnit,productChangeinvalue,productNAVPriceDate,productNAVValue,productAllocPercent,productSurrCrdtHdr,productSurrChrgHdr,productBaseCOICharge,productRiderCOICharge,productFlatExtraCOICharge,productAdminFeeChrg,productUnitRecordType,productTopupLayerNo,productTrans_Fundcode, row_number() over(partition by productid,producteventid,producttransid order by productid,producteventid,producttransid) rn

  from product

  )

  Select productid,producteventid,producttransid, '+ @ColumnHeaders + ' from mycte Group by productid,producteventid,producttransid ';

  print @query

  execute sp_executesql @query;

  getting error is :

  Incorrect syntax near 'Max'.

  could you please help on it.

• Jeff Moden

  SSC Guru

  Points: 1003859

  More actions

  March 27, 2020 at 2:14 pm

  #3738253

  Please... especially when posting a shedload of code, either learn how to post the code in a code window or attach it all as a txt file.  When constructing a question, look for the following in the tool bar at the menu at the top of the message window.

  

  For large amount of code like you have, it would be a lot easier for people to help you if you saved your code as a txt file and uploaded it as an attachment near the bottom of the message window.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sebastiaan.thompson

  SSC Journeyman

  Points: 92

  More actions

  April 2, 2020 at 12:33 pm

  #3739952

  The initial problem was caused by the following section in the code:

  + char(10)+char(13) +','

  +',' + '

  Increasing @query and @ColumnHeaders from nvarchar(4000) to nvarchar(max) resolved the remaining issues.

• sebastiaan.thompson

  SSC Journeyman

  Points: 92

  More actions

  April 2, 2020 at 12:34 pm

  #3739955

  Please find attached the modified code

  Attachments:

  You must be logged in to view attached files.

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  April 2, 2020 at 4:33 pm

  #3740025

  Ouch... had to reformat all that so it was easier to read...

  IF OBJECT_ID('product') IS NOT NULL
  DROP TABLE [dbo].[product]
  
  CREATE TABLE [dbo].[product](
  [productid] [VARCHAR](20) NULL,
  [productEventID] [VARCHAR](50) NULL,
  [productTransID] [VARCHAR](50) NULL,
  [productEffectiveDate] [DATETIME] NULL,
  [productInvestmentID] [VARCHAR](20) NULL,
  [productBuySellCode] [VARCHAR](10) NULL,
  [productClosingValue] [DECIMAL](13, 2) NULL,
  [productClosingUnits] [INT] NULL,
  [productChangeInUnit] [VARCHAR](20) NULL,
  [productChangeinvalue] [VARCHAR](20) NULL,
  [productNAVPriceDate] [DATETIME] NULL,
  [productNAVValue] [DECIMAL](13, 2) NULL,
  [productAllocPercent] [DECIMAL](13, 2) NULL,
  [productSurrCrdtHdr] [DECIMAL](13, 2) NULL,
  [productSurrChrgHdr] [DECIMAL](13, 2) NULL,
  [productBaseCOICharge] [DECIMAL](13, 2) NULL,
  [productRiderCOICharge] [DECIMAL](13, 2) NULL,
  [productFlatExtraCOICharge] [DECIMAL](13, 2) NULL,
  [productAdminFeeChrg] [DECIMAL](13, 2) NULL,
  [productAllocationCharge] [DECIMAL](13, 2) NULL,
  [productUnitRecordType] [VARCHAR](2) NULL,
  [productTopupLayerNo] [INT] NULL,
  [productTrans_Fundcode] [VARCHAR](10) NULL
  ) ON [PRIMARY]
  
  INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'101', CAST(N'2020-03-12T00:00:00.000' AS DATETIME), N'Fund2', N'B', CAST(7202.00 AS DECIMAL(13, 2)), 150, N'150', N'4700.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(19.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'mobile', N'105', CAST(N'2020-03-14T00:00:00.000' AS DATETIME), N'Fund4', N'S', CAST(19339.91 AS DECIMAL(13, 2)), 100, N'100', N'5000', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(17.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'107', CAST(N'2020-03-13T00:00:00.000' AS DATETIME), N'Fund2', N'S', CAST(6802.96 AS DECIMAL(13, 2)), 150, N'400', N'100.043', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(19.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'100', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund2', N'B', CAST(7520.00 AS DECIMAL(13, 2)), 150, N'150', N'7520.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(19.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'102', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund3', N'S', CAST(8001.91 AS DECIMAL(13, 2)), 100, N'100', N'1000.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(17.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0005')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'100', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund1', N'B', CAST(3760.00 AS DECIMAL(13, 2)), 200, N'200', N'3760.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(18.50 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'101', CAST(N'2020-03-12T00:00:00.000' AS DATETIME), N'Fund4', N'B', CAST(19739.00 AS DECIMAL(13, 2)), 150, N'150', N'4700.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(16.30 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'100', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund4', N'B', CAST(15040.00 AS DECIMAL(13, 2)), 150, N'150', N'15040.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(16.30 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'101', CAST(N'2020-03-12T00:00:00.000' AS DATETIME), N'Fund1', N'B', CAST(8699.00 AS DECIMAL(13, 2)), 200, N'200', N'4700.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(18.50 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DATETIME), N'Fund1', N'S', CAST(8244.00 AS DECIMAL(13, 2)), 200, N'', N'75', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(18.50 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'107', CAST(N'2020-03-13T00:00:00.000' AS DATETIME), N'Fund1', N'S', CAST(8299.48 AS DECIMAL(13, 2)), 200, N'400', N'300.522', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(18.50 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'101', CAST(N'2020-03-12T00:00:00.000' AS DATETIME), N'Fund3', N'B', CAST(12701.91 AS DECIMAL(13, 2)), 100, N'100', N'4700.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(17.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0003')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'102', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund1', N'S', CAST(3499.48 AS DECIMAL(13, 2)), 200, N'200', N'1', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(18.50 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'106', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund2', N'S', CAST(7000.96 AS DECIMAL(13, 2)), 150, N'150', N'519.043', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(19.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'mobile', N'104', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund4', N'S', CAST(9001.91 AS DECIMAL(13, 2)), 100, N'100', N'5000', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(17.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), N'N', 0, N'F0002')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'106', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund4', N'S', CAST(14001.91 AS DECIMAL(13, 2)), 150, N'150', N'1038.086', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(16.30 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DATETIME), N'Fund1', N'B', CAST(8434.00 AS DECIMAL(13, 2)), 200, N'200', N'190', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(18.50 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'102', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund2', N'B', CAST(2502.00 AS DECIMAL(13, 2)), 150, N'150', N'502', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(19.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0003')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DATETIME), N'Fund2', N'B', CAST(6687.00 AS DECIMAL(13, 2)), 150, N'150', N'110', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(19.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0003')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'107', CAST(N'2020-03-13T00:00:00.000' AS DATETIME), N'Fund4', N'S', CAST(19439.91 AS DECIMAL(13, 2)), 150, N'400', N'100.086', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(16.30 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pad', N'106', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund1', N'S', CAST(3500.48 AS DECIMAL(13, 2)), 200, N'200', N'259.522', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(18.50 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), N'N', 0, N'F0001')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DATETIME), N'Fund2', N'S', CAST(6577.00 AS DECIMAL(13, 2)), 150, N'150', N'75', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(19.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0004')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DATETIME), N'Fund4', N'S', CAST(1924.00 AS DECIMAL(13, 2)), 150, N'150', N'75', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(16.30 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0006')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'103', CAST(N'2020-03-15T00:00:00.000' AS DATETIME), N'Fund3', N'S', CAST(12226.00 AS DECIMAL(13, 2)), 100, N'100', N'75', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(17.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0005')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'pen', N'102', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund4', N'S', CAST(15039.00 AS DECIMAL(13, 2)), 150, N'150', N'1.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(16.30 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0006')INSERT [dbo].[product] ([productid], [productEventID], [productTransID], [productEffectiveDate], [productInvestmentID], [productBuySellCode], [productClosingValue], [productClosingUnits], [productChangeInUnit], [productChangeinvalue], [productNAVPriceDate], [productNAVValue], [productAllocPercent], [productSurrCrdtHdr], [productSurrChrgHdr], [productBaseCOICharge], [productRiderCOICharge], [productFlatExtraCOICharge], [productAdminFeeChrg], [productAllocationCharge], [productUnitRecordType], [productTopupLayerNo], [productTrans_Fundcode]) VALUES (N'1', N'rin', N'100', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), N'Fund3', N'B', CAST(11280.00 AS DECIMAL(13, 2)), 100, N'100', N'11280.0', CAST(N'2020-02-19T00:00:00.000' AS DATETIME), CAST(17.60 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(0.00 AS DECIMAL(13, 2)), CAST(100.00 AS DECIMAL(13, 2)), N'N', 0, N'F0003')
  
  DECLARE @query NVARCHAR(MAX) =NULL
  DECLARE @ColumnHeaders NVARCHAR(MAX) ;
  
  SET @ColumnHeaders = STUFF( (SELECT ','
  
  + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productinvestmentid else null end ) as ' + QUOTENAME('productinvestmentid'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productBuySellCode else null end ) as ' + QUOTENAME('productBuySellCode'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productEffectiveDate else null end ) as ' + QUOTENAME('productEffectiveDate'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productClosingValue else null end ) as ' + QUOTENAME('productClosingValue'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productAllocationCharge else null end ) as ' + QUOTENAME('productAllocationCharge'+CAST([t].[rn] AS VARCHAR(10)),'[') + CHAR(10)+CHAR(13) +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productClosingUnits else null end ) as ' + QUOTENAME('productClosingUnits'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productChangeInUnit else null end ) as ' + QUOTENAME('productChangeInUnit'+CAST([t].[rn] AS VARCHAR(10)),'[')
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productChangeinvalue else null end ) as ' + QUOTENAME('productChangeinvalue'+CAST([t].[rn] AS VARCHAR(10)),'[')
  + CHAR(10)+CHAR(13)
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productNAVPriceDate else null end ) as ' + QUOTENAME('productNAVPriceDate'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productNAVValue else null end ) as ' + QUOTENAME('productNAVValue'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productAllocPercent else null end ) as ' + QUOTENAME('productAllocPercent'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productSurrCrdtHdr else null end ) as ' + QUOTENAME('productSurrCrdtHdr'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productSurrChrgHdr else null end ) as ' + QUOTENAME('productSurrChrgHdr'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productBaseCOICharge else null end ) as ' + QUOTENAME('productBaseCOICharge'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13) +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productRiderCOICharge else null end ) as ' + QUOTENAME('productRiderCOICharge'+CAST([t].[rn] AS VARCHAR(10)),'[') + CHAR(10)+CHAR(13) +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productFlatExtraCOICharge else null end ) as ' + QUOTENAME('productFlatExtraCOICharge'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13) +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productAdminFeeChrg else null end ) as ' + QUOTENAME('productAdminFeeChrg'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13) +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productUnitRecordType else null end ) as ' + QUOTENAME('productUnitRecordType'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13) +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productTopupLayerNo else null end ) as ' + QUOTENAME('productTopupLayerNo'+CAST([t].[rn] AS VARCHAR(10)),'[') + CHAR(10)+CHAR(13)
  
  +',' + 'Max(CASE WHEN rn =' + QUOTENAME([t].[rn],'''') + ' THEN productTrans_Fundcode else null end ) as ' + QUOTENAME('productTrans_Fundcode'+CAST([t].[rn] AS VARCHAR(10)),'[')
  
  + CHAR(10)+CHAR(13) FROM (
  
  SELECT [productid]
  ,[productEventID]
  ,[productTransID]
  ,[productInvestmentID]
  ,[productBuySellCode]
  ,[productEffectiveDate]
  ,[productClosingValue]
  ,[productAllocationCharge]
  ,[productClosingUnits]
  ,[productChangeInUnit]
  ,[productChangeinvalue]
  ,[productNAVPriceDate]
  ,[productNAVValue]
  ,[productAllocPercent]
  ,[productSurrCrdtHdr]
  ,[productSurrChrgHdr]
  ,[productBaseCOICharge]
  ,[productRiderCOICharge]
  ,[productFlatExtraCOICharge]
  ,[productAdminFeeChrg]
  ,[productUnitRecordType]
  ,[productTopupLayerNo]
  ,[productTrans_Fundcode]
  , ROW_NUMBER() OVER(PARTITION BY [productid],[productEventID],[productTransID] 
  ORDER BY [productid],[productEventID],[productTransID]) [rn]
  FROM [product]) AS [t] 
  GROUP BY [t].[rn] 
  ORDER BY [t].[rn] 
  FOR XML PATH(''), TYPE).[value]('.', 'varchar(max)'), 1, 1, '');
  
  PRINT @ColumnHeaders
  
  SET @query =N';with mycte as (
  select productid
  ,producteventid
  ,producttransid
  ,productinvestmentid
  ,productBuySellCode
  ,productEffectiveDate
  ,productClosingValue
  ,productAllocationCharge
  ,productClosingUnits
  ,productChangeInUnit
  ,productChangeinvalue
  ,productNAVPriceDate
  ,productNAVValue
  ,productAllocPercent
  ,productSurrCrdtHdr
  ,productSurrChrgHdr
  ,productBaseCOICharge
  ,productRiderCOICharge
  ,productFlatExtraCOICharge
  ,productAdminFeeChrg
  ,productUnitRecordType
  ,productTopupLayerNo
  ,productTrans_Fundcode
  , row_number() over(partition by productid, producteventid, producttransid order by productid,producteventid,producttransid) rn
  from product
  )
  
  Select productid,producteventid,producttransid, '+ @ColumnHeaders + N' from mycte Group by productid,producteventid,producttransid ';
  
  PRINT @query
  
  EXECUTE [sys].[sp_executesql] @query;

• Phil Parkin

  SSC Guru

  Points: 246693

  More actions

  April 2, 2020 at 4:59 pm

  #3740028

  A quick couple of comments.

  IF OBJECT_ID('product') IS NOT NULL

  DROP TABLE [dbo].[product]

  May be replaced by DROP TABLE IF EXISTS [dbo].[product].

  Not only that, but if you had a proc in the database called dbo.Product, but no table, this code would fail. The fuller syntax is

  IF OBJECT_ID('product', 'U') IS NOT NULL ...

  where U stands for User-defined table.

   

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.