Pivot example

Vijay Kashyap Algo Trading Expert

 --drop table #Final


CREATE TABLE #Final

(

[PUNBR] [VARCHAR] (3) NULL,

[GRNBR] [VARCHAR] (3) NULL,

[GroupName] [VARCHAR](30) NULL,

[IsPast] [BIT] NULL,

[PRODUCT] [VARCHAR] (30) NULL,

[IsIProduct] [INT] NULL,

[ProductName] [VARCHAR](9) NULL,

[ProductDescription] [VARCHAR](30) NULL,

[ProductSortBy] [INT] NULL,

[DataSortBy] [INT] NULL,

[AdjSortBy] [INT] NULL,

[ESSN] [VARCHAR] (9) NULL,

[CERT] [VARCHAR] (12) NULL,

[EmpName] [VARCHAR] (70) NULL,

[STATUS] [VARCHAR] (1) NULL,

[BENEFIT] [VARCHAR] (10) NULL,

[DEP] [VARCHAR] (7) NULL,

[BILL_PLAN] [VARCHAR] (9) NULL,

[PREM] [MONEY] NULL,

[IsCAP] [INT] NULL,

[PREM_CAP] [MONEY] NULL

)



-- Dummy data for #Final table

INSERT INTO #Final VALUES ('001', '101', 'GroupA', 1, 'Medical', 0, 'Medical', 'DescriptionA', 1, 10, 100, 'E001', 'CERT001', 'John Doe', 'A', 'BenefitA', 'DepA', 'PlanA', 500.00, 1, 100.00);

INSERT INTO #Final VALUES ('002', '102', 'GroupB', 0, 'Dental',  1, 'Dental', 'DescriptionB', 2, 20, 200, 'E001', 'CERT001', 'Jane Smith', 'A', 'BenefitB', 'DepB', 'PlanB', 600.00, 0, 0.00);

INSERT INTO #Final VALUES ('003', '103', 'GroupC', 1, 'Medical', 0, 'Medical', 'DescriptionC', 3, 30, 300, 'E003', 'CERT003', 'Bob Johnson', 'I', 'BenefitC', 'DepC', 'PlanC', 700.00, 1, 150.00);

INSERT INTO #Final VALUES ('003', '104', 'GroupC', 1, 'Dental',  0, 'Dental', 'DescriptionC', 3, 30, 300, 'E003', 'CERT003', 'Bob Johnson', 'I', 'BenefitC', 'DepC', 'PlanC', 700.00, 1, 150.00);

INSERT INTO #Final VALUES ('003', '105', 'GroupC', 1, 'Dental',  0, 'Dental', 'DescriptionC', 3, 30, 300, 'E003', 'CERT003', 'Bob Johnson', 'I', 'BenefitC', 'DepC', 'PlanC', 700.00, 1, 150.00);



-- Add more dummy data as needed

SELECT *

FROM (

    SELECT PRODUCT, ESSN, CERT, PREM

    FROM #Final

) AS SourceTable

PIVOT (

    SUM(PREM)

    FOR PRODUCT IN ([Medical], [Dental]) -- Add more products as needed

) AS PivotTable;



SELECT PRODUCT, ESSN, CERT, PREM from #Final