How to convert rows to columnsin sql server

Transact-SQL
1

Hi i have one doubt in sql server how to convert rows to columns dynamically in sql server
CREATE TABLE [dbo].[product](
[productid] varchar NULL,
[productEventID] varchar NULL,
[productTransID] varchar NULL,
[productEffectiveDate] [datetime] NULL,
[productInvestmentID] varchar NULL,
[productBuySellCode] varchar NULL,
[productClosingValue] [decimal](13, 2) NULL,
[productClosingUnits] [int] NULL,
[productChangeInUnit] varchar NULL,
[productChangeinvalue] varchar 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 NULL,
[productTopupLayerNo] [int] NULL,
[productTrans_Fundcode] varchar 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 :

2

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.
3

please go in small bits from top to bottom

please go one thing at a time

then it becomes EASY

working and not working
try to remove stuff to identify what not working

4

What is the final result you want to see. Please test the script you posted first?