Click here to Skip to main content
15,302,859 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

SQL
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]




SQL
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'

SQL
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
Posted
Updated 26-Feb-13 0:20am
v2

1 solution

I've also faced problem while doing this kind of output; This link[^] might help you to understand all about PIVOT.
   
Comments
Nirali R shah 26-Feb-13 8:09am
   
thanks for your reply but actually i havent any problem with pivot but is there any other way to convert rows into columns in sql server. becuase PIVOT is not giving me 100% output as per my requirement..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900