Select row data as ColumnName and Value

Question

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?

Answer 1

You could try using a UNION

Test Data

DECLARE @TABLE1_HISTORY TABLE (
   ID INT, 
   NAME VARCHAR(50), 
   TYPE VARCHAR(50), 
   TRANSACTION_ID VARCHAR(50))


INSERT INTO @TABLE1_HISTORY
SELECT 1, 'Joe', 'INSERT', 'a'
UNION ALL SELECT 1, 'Bill', 'UPDATE', 'b'
UNION ALL SELECT 1, 'Bill', 'DELETE', 'c'

SQL Statement

SELECT    [TransactionID] = Transaction_ID
          , [Type] = [Type]
          , [ColumnName] = 'ID'
          , [ColumnValue] = CAST(ID AS VARCHAR(50))
FROM      @Table1_History
UNION ALL
SELECT    [TransactionID] = Transaction_ID
          , [Type] = [Type]
          , [ColumnName] = 'NAME'
          , [ColumnValue] = [Name]
FROM      @Table1_History
ORDER BY  TransactionID
          , ColumnName