How can I retrieve the values from a generic audit table reconstituted as a rowset with meaningful column names in SQL?

Question

Hi gang,

So I've got this audit table, looks like this:

USE [DatabaseName]
GO
/****** Object:  Table [ERAUser].[Audit]    Script Date: 05/20/2009 17:07:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ERAUser].[Audit](
    [AuditID] [int] IDENTITY(1,1) NOT NULL,
    [Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TableName] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PrimaryKeyField] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PrimaryKeyValue] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FieldName] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OldValue] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NewValue] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [UpdateDate] [datetime] NULL DEFAULT (getdate()),
    [UserName] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

The problem is, for reasons out of my control, I need to return data to users (other developers that are the users of this system) as rowsets that replicate the source table. How can I turn this schema on its side and get the values in FieldName as the column headings for a rowset? I'm using SQL 2005. I will know the tablename and the UpdateDate.

Answer 1

This can be done using the PIVOT function. I'll try to work out an example:

SELECT *
FROM ERAUser.Audit
PIVOT (max(NewValue) FOR FieldName in (Field1, Field2, Field3)) as PivotTable

The max() is necessary to tell Sql Server what to do if it finds multiple rows with the same FieldName. You can use a WHERE statement to limit it to the right rows; if you ensure it finds only one, max(NewValue) is equal to NewValue.

You can generate the SQL for this, if you have a long list of columns:

declare @columnlist nvarchar(4000)
select @columnlist = IsNull(@columnlist + ', ', '') + FieldName
from (
    select distinct FieldName from ERAUser.Audit
) sub

declare @query nvarchar(4000)
select @query = 'select *
from ERAUser.Audit
PIVOT (max(newValue) FOR FieldName in (' + @columnlist + ')) as PivotTable'

exec sp_executesql @query

Here's a basic example of PIVOT, to get the general idea:

create table #normalized (
    colname varchar(12),
    value varchar(12)
)

insert into #normalized values ('value1','A')
insert into #normalized values ('value2','B')
insert into #normalized values ('value3','C')

select *
from #normalized
PIVOT (max(value) FOR ColName in (value1,value2,value3)) as Y

This will result in:

value1    value2    value3
A         B         C

Answer 2

It will be extremely difficult, not least because the type of the values for one combination of table name and column name will be different from other types. To create a whole record corresponding to one row of the table, you are going to have to combine multiple audit records. So, your rowset would have to be tailored to one table at a time. It isn't clear whether you record the unchanged columns - you probably don't - so dealing with a rowset containing the entire record will be tough, at best.

Answer 3

If you know that you only need to return the rows from one table (e.g. "Foo") and assuming all fields to create are varchar(50):

1) Create a temp table with the structure you need

DECLARE @Sql VARCHAR(8000)
DECLARE @ColName VARCHAR(128)
DECLARE @Comma VARCHAR(2)

SET @CreateSql = "CREATE TABLE #Temp ("
SET @Comma = ''

DECLARE columnCur CURSOR FOR
SELECT DISTINCT FieldName
FROM Audit
WHERE TableName = 'Foo'

OPEN columnCur

FETCH NEXT FROM columnCur INTO @ColName

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @CreateSql = @CreateSql+@Comma+@ColName+' VARCHAR(128) NULL'
  SET @Comma = ', '
  FETCH NEXT FROM columnCur INTO @ColName
END

CLOSE columnCur
DEALLOCATE columnCur

SET @CreateSql = @CreateSql + ")"

EXECUTE sp_executesql @CreateSql

2) Fill in the table primary keys:

--Assuming a single PK column, otherwise you need another cursor

DECLARE @pk VARCHAR(1000)

SELECT @pk=PrimaryKeyField
FROM Audit
WHERE TableName = 'Foo'

DECLARE @PkSql VARCHAR(2000)
SET @PkSql = 'INSERT INTO #Temp ('+@pk+') VALUES SELECT DISTINCT PrimaryKeyValue FROM Audit WHERE TableName = ''Foo'''

EXECUTE sp_executesql @PkSql

3) Create a cursor that creates dynamic sql that updates the rows in the temp table with the correct values (similar style as above)

4) SELECT from #Temp

5) DROP #Temp

Note: This should work, but it's totally untested as I don't have a SQL Server available right now.