Click here to Skip to main content
12,452,344 members (51,432 online)
Rate this:
 
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 25-Feb-13 23:42pm
Updated 26-Feb-13 0: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 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 10 Jun 2016
Copyright © CodeProject, 1999-2016
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