Select row data as ColumnName and Value
- by Bobcat1506
I have a history table and I need to select the values from this table in ColumnName, ColumnValue form. I am using SQL Server 2008 and I wasn’t sure if I could use the PIVOT function to accomplish this. Below is a simplified example of what I need to accomplish:
This is what I have:
The table’s schema is
CREATE TABLE TABLE1 (ID INT PRIMARY KEY, NAME VARCHAR(50))
The “history” table’s schema is
CREATE TABLE TABLE1_HISTORY(
ID INT,
NAME VARCHAR(50),
TYPE VARCHAR(50),
TRANSACTION_ID VARCHAR(50))
Here is the data from TABLE1_HISTORY
ID NAME TYPE TRANSACTION_ID
1 Joe INSERT a
1 Bill UPDATE b
1 Bill DELETE c
I need to extract the data from TABLE1_HISTORY into this format:
TransactionId Type ColumnName ColumnValue
a INSERT ID 1
a INSERT NAME Joe
b UPDATE ID 1
b UPDATE NAME Bill
c DELETE ID 1
c DELETE NAME Bill
Other than upgrading to Enterprise Edition and leveraging the built in change tracking functionality, what is your suggestion for accomplishing this task?
