Category: Scripts

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.

TSQL – Last Time Table was Updated

If you looking for a script to find out when was the last time a particular table was updated then here it is:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DATABASENAME')
AND OBJECT_ID=OBJECT_ID('TABLENAME')

SELECT
tbl.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_db_index_usage_stats ius INNER JOIN
sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID()

This normally happens when you don’t have columns such as DateCreated and UpdatedDate in the table.

Live as if you were to die tomorrow. Learn as if you were to live forever.. -Mahatma Gandhi