Month: July 2020

A Pivot table in TSQL is useful to spread row data as columns. Row values can be defined into multiple columns.

A row in SQL Server represents an entity and a column as an attribute of the entity.

To build a pivot table, you will need to define three columns.

• Column 1 – Row grouping
• Column 2 – Create new columns based on the values
• Column 3 – Aggregated values for the row and columns

Steps to build a pivot table:

1. Select based dataset
2. Create temporary result set using Common Expression Table (CTE)
3. Apply PIVOT operator

Example

This is the original table

Rate_Id	Desc	R01	R02	R03	R04
1	Plan	14.10	25.20	28.50	40.50
2	Plan	21.50	39.50	41.80	60.90

This will be the final result

Plan	Silver	Gold
Plan-1	14.10	21.50
Plan-2	25.20	39.50
Plan-3	28.50	41.80
Plan-4	40.50	60.90

Step 1: Create the table

CREATE TABLE #Rate (
Rate_Id int,
Rate_Desc varchar(100),
R01 decimal(17,2),
R02 decimal(17,2),
R03 decimal(17,2),
R04 decimal(17,2)
);

Step 2: Insert records

INSERT INTO #Rate VALUES (1,'Plan',14.10,25.20,28.50,40.50);
INSERT INTO #Rate VALUES (2,'Plan',21.50,39.50,41.80,60.90);

Step 3: Use cross apply to create additional rows

SELECT * FROM #Rate
cross apply
(
   select 1,R01 union
   select 2,R02 union
   select 3,R03 union
   select 4,R04 
) r (RowNo, Rate)

Output

Rate_Id	Rate_Desc	R01	R02	R03	R04	RowNo	Rate
1	Plan	14.10	25.20	28.50	40.50	1	14.10
1	Plan	14.10	25.20	28.50	40.50	2	25.20
1	Plan	14.10	25.20	28.50	40.50	3	28.50
1	Plan	14.10	25.20	28.50	40.50	4	40.50
2	Plan	21.50	39.50	41.80	60.90	1	21.50
2	Plan	21.50	39.50	41.80	60.90	2	39.50
2	Plan	21.50	39.50	41.80	60.90	3	41.80
2	Plan	21.50	39.50	41.80	60.90	4	60.90

Step 4: Use PIVOT to change rows to columns

WITH CTE_Rates As (
SELECT * FROM #Rate
cross apply
(
   select 1,R01 union
   select 2,R02 union
   select 3,R03 union
   select 4,R04 
) r (RowNo, Rate))
SELECT [Plan], [1] as [Silver], [2] as [Gold]
FROM (
SELECT Rate_Id,CONCAT(Rate_Desc,'-',RowNo) as [Plan], Rate_Desc, RowNo,Rate
FROM CTE_Rates) r
PIVOT(
 Max(Rate)
  for Rate_Id IN ([1],[2])
) p
;

Final table result from Step 4

Plan	Silver	Gold
Plan-1	14.10	21.50
Plan-2	25.20	39.50
Plan-3	28.50	41.80
Plan-4	40.50	60.90

In this example I have converted Rate_Id 1 and 2 as Silver and Gold. Also R01, R02, R03, R04 as Plan-1, Plan-2, Plan-3, Plan-4.