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