Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Delphi
Hello...
I am a Delphi Developer.
i have a query for master / detail table, which is for Audit record. now i want to generate a report for Audit Trail using this query with the help of Grid in delphi. Is there any properties in grid that i can use for the same. i am using dev express components.
Thanks a lot for any help...
 
my sql is
 
declare @col varchar(max)
declare @col1 varchar(max)
declare @convert varchar(max)
 
select @col = STUFF((select distinct '],[' + Audit_field_name  from tblAuditTrail
                WHERE  AUDIT_ACTION_TYPE = 'iNSERT' --AND AUDIT_ACTION_TIME = ''
                AND AUDIT_USER_ID = '1' AND AUDIT_TBL_NAME = 'PUR_ORD'
                for xml path ('')), 1,2, '') + ']'
 
select @col1 = STUFF((select distinct '],[' + Audit_field_name  from tblAuditTrail
                WHERE  AUDIT_ACTION_TYPE = 'iNSERT' --AND AUDIT_ACTION_TIME = ''
                AND AUDIT_USER_ID = '1' AND AUDIT_TBL_NAME = 'PUR_ORD_items'
                for xml path ('')), 1,2, '') + ']'
 
--select @col
set @convert = 'select * from
(select AUDIT_TBLDETAIL_PKID,audit_new_value,audit_field_name from tblaudittrail
         where AUDIT_TBLDETAIL_PKID > 0 and AUDIT_TBLMASTER_PKID = 0) as tblaudittrail
    pivot(max(audit_new_value) for audit_field_name
    in (' + @col + ')) as pivottable
 
    union all
 
    select * from
(select AUDIT_TBLDETAIL_PKID,audit_new_value,audit_field_name from tblaudittrail
         where AUDIT_TBL_NAME LIKE ''PUR_ORD_items'') as tblaudittrail
    pivot(max(audit_new_value) for audit_field_name
    in (' + @col + ')) as pivottable'
 
     execute(@convert)
Posted 14-Feb-13 23:44pm

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

  Print Answers RSS
0 OriginalGriff 6,045
1 DamithSL 4,611
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 15 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