convert dynamic rows to columns in sql server

Question

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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 · Answer 2

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 · Answer 3

Please find attached the modified code

Attachments:

You must be logged in to view attached files.

pietlinden SSC Guru Points: 63503 More actions · Answer 4

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: 246960 More actions · Answer 5

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin