convert rows to columns

See more:

SQL-Server-2008R2

Hello...
I want to convert rows to columns in sql server for Audit Trail Report. I have done with Pivot , but there is a issue when the case of Update. means when i update any item multiple times it fetch only one row, i want all updated track of that item.

I have a table named tblaudittrail, which contains

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblAuditTrail](
    [AUDIT_ID] [int] IDENTITY(1,1) NOT NULL,
    [AUDIT_TBL_NAME] [nvarchar](50) NOT NULL,
    [AUDIT_FIELD_NAME] [nvarchar](50) NULL,
    [AUDIT_OLD_VALUE] [nvarchar](50) NULL,
    [AUDIT_NEW_VALUE] [nvarchar](50) NULL,
    [AUDIT_FIELD_TYPE] [nvarchar](50) NOT NULL,
    [AUDIT_ACTION_TYPE] [int] NOT NULL,
    [AUDIT_ACTION_TIME] [date] NULL,
    [AUDIT_USER_ID] [nvarchar](50) NOT NULL,
    [AUDIT_TBLMASTER_PKID] [int] NULL,
    [AUDIT_TBLDETAIL_PKID] [int] NULL,
 CONSTRAINT [PK_tblAuditTrail] PRIMARY KEY CLUSTERED
(
    [AUDIT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT 
INTO [dbo].[tblAuditTrail]
           ([AUDIT_TBL_NAME]
           ,[AUDIT_FIELD_NAME]
           ,[AUDIT_OLD_VALUE]
           ,[AUDIT_NEW_VALUE]
           ,[AUDIT_FIELD_TYPE]
           ,[AUDIT_ACTION_TYPE]
           ,[AUDIT_ACTION_TIME]
           ,[AUDIT_USER_ID]
           ,[AUDIT_TBLMASTER_PKID]
           ,[AUDIT_TBLDETAIL_PKID])
     VALUES
           ('PUR_ORD','PO_ID','NULL','1235','int',0,'2013-02-25','1',0,1235)

('PUR_ORD_ITEMS','PO_IT_QTY','NULL','3.000','numeric(10,3)',0,'2013-02-25','1',1235,56914)
('PUR_ORD_ITEMS','PO_IT_FREE_QTY','NULL','0.000','numeric(10,3)',0,'2013-02-25','1',1235,56914)
('PUR_ORD_ITEMS','PO_IT_FREE_QTY','0.000','4.000','numeric(10,3)',1,'2013-02-25','1',1235,56914)
('PUR_ORD_ITEMS','PO_IT_FREE_QTY','4.000','10.000','numeric(10,3)',1,'2013-02-25','1',1235,56914)
('PUR_ORD_ITEMS','PO_IT_FREE_QTY','10.000','20.000','numeric(10,3)',1,'2013-02-25','1',1235,56914)

where Action_type : 0= insert ,1 = update
this pivot query create issue when there are more update rows for the same item '56914'

declare @col1 varchar(max)
declare @convert1 varchar(max)

select @col1 = STUFF((SELECT '],[' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME ='PUR_ORD_items' for xml path ('')),1,2, '') + ']'


set @convert1 = 'select * from
(select AUDIT_TBLMASTER_PKID as ID, AUDIT_TBLDETAIL_PKID AS DETAILID,AUDIT_ACTION_TYPE,
case AUDIT_ACTION_TYPE when 0 then ''INSERT'' when 1 then ''UPDATE'' else ''DELETE'' end as ActionType,
 audit_new_value,audit_field_name from tblaudittrail
         where AUDIT_TBL_NAME = ''PUR_ORD_items'') as tblaudittrail
    pivot(max(audit_new_value) for audit_field_name
    in (' + @col1 + ')) as pivottable order by DETAILID, AUDIT_ACTION_TYPE'

    execute(@convert1)

please do needful .. thanks in advance