How can I get the values from a shared audit table restored as a rowset with meaningful column names in SQL?
So, I have this audit table, it 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 that for reasons beyond my control, I need to return data to users (other developers who are users of this system) as rowsets that replicate the original table. How can I turn this schema on my side and get the values in FieldName as column headers for a rowset? I am using SQL 2005. I will know the table_name and UpdateDate.
a source to share
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
max () needs to tell the Sql server what to do if it finds multiple rows with the same FieldName. You can use the WHERE clause to restrict it to the correct strings; if you make sure it only finds one, max (NewValue) is equal to NewValue.
You can create 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 PIVOT example 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 lead to:
value1 value2 value3
A B C
a source to share
This will be extremely difficult, not least because the value type for one combination of table name and column name will be different from other types. To create an entire record corresponding to a single row in the table, you will have to concatenate multiple audit records. So your rowset needs to be adapted to one table at a time. It's unclear if you are writing immutable columns - you probably don't, so dealing with a rowset containing the entire record will be more difficult at best.
a source to share
If you know that you only need to return rows from one table (eg "Foo") and if all fields to create are varchar (50):
1) Create a temporary 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 primary keys of the table:
--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 a 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 hasn't been fully tested since I don't have SQL Server right now.
a source to share