将行转换为列 [英] convert rows to columns

问题描述

您好...

我想将行转换为sql server中的列以用于审计跟踪报告。我已经完成了Pivot，但是在Update的情况下存在问题。意味着当我多次更新任何项目时，它只获取一行，我想要该项目的所有更新的轨道。



我有一张名为tblaudittrail的表，其中包含

 < span class =code-keyword> 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   ñ ULL ，
 [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 = < span class =code-keyword> 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'，'数字（10,3）'，0，'2013-02-25'， '1'，1235,56914）

（'PUR_ORD_ITEMS'，'PO_IT_FREE_QTY'，'NULL'，'0.000'，'数字（10,3）'，0，'2013-02-25 '，'1'，1235,56914）

（'PUR_ORD_ITEMS'，'PO_IT_FREE_QTY'，'0.000'，'4.000'，'数字（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年2月25日'， '1'，1235,56914）


其中Action_type：0 = insert，1 = update

如果同一项目有更多更新行，则此数据透视查询会产生问题'56914 '

 声明  @ col1   varchar （max）
 声明  @ convert1   varchar （max）
 
 选择  @ col1  = STUFF（（ SELECT  ' ]，[' + COLUMN_NAME  FROM  INFORMATION_SCHEMA.COLUMNS 
  WHERE  TABLE_NAME = '  PUR_ORD_items'  for  xml path（' '））， 1 ， 2 ，' '）+ ' ]' 
 
 
  set   @ convert1  = '  select * from 
（选择AUDIT_TBLMASTER_PKID作为ID，AUDIT_TBLDETAIL_PKID AS DETAILID，AUDIT_ACTION_TYPE，
案例AUDIT_ACTION_TYPE为0然后''INSERT''当1然后''UPDATE''否则''DELETE''以ActionType结尾，
 audit_new_value，audit_field_name来自tblaudittrail 
其中AUDIT_TBL_NAME =''PUR_ORD_items''）为tblaudittrail 
 pivot（audit_field_name的最大值（audit_new_value）
 in（' +  @ col1  + ' ））由DETAILID，AUDIT_ACTION_作为可枢转的订单TYPE' 
 
 执行（ @ convert1 ）

请确实需要..提前感谢

解决方案

在做这种输出时我也遇到了问题;这个链接 [ ^ ]可能会帮助您了解PIVOT的所有信息。

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

解决方案

I've also faced problem while doing this kind of output; This link[^] might help you to understand all about PIVOT.

这篇关于将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！