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_IdDescR01R02R03R04
1Plan14.1025.2028.5040.50
2Plan21.5039.5041.8060.90

This will be the final result

PlanSilverGold
Plan-114.1021.50
Plan-225.2039.50
Plan-328.5041.80
Plan-440.5060.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_IdRate_DescR01R02R03R04RowNoRate
1Plan14.1025.2028.5040.50114.10
1Plan14.1025.2028.5040.50225.20
1Plan14.1025.2028.5040.50328.50
1Plan14.1025.2028.5040.50440.50
2Plan21.5039.5041.8060.90121.50
2Plan21.5039.5041.8060.90239.50
2Plan21.5039.5041.8060.90341.80
2Plan21.5039.5041.8060.90460.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

PlanSilverGold
Plan-114.1021.50
Plan-225.2039.50
Plan-328.5041.80
Plan-440.5060.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.

Leave a Reply

Your email address will not be published. Required fields are marked *