Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
Posted 26-Feb-13 0:42am
Edited 26-Feb-13 1:20am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I've also faced problem while doing this kind of output; This link[^] might help you to understand all about PIVOT.
  Permalink  
Comments
Nirali R shah at 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)



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 26 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100