将列值更改为SQL Server中的可读值 [英] Change a column value to a readable value in SQL server

问题描述

请指教 -

我有一个基本的审计表，用于跟踪名为TableM的数据库表中已插入，已更新或已删除的项目。

我创建了触发器来记录插入，更新或删除行的人的操作，并且该记录进入名为TransactAudit的表中。 TransactAudit表有一个名为Operation的列，它记录插入项目的I，更新项目的U和已删除项目的D。

我想创建一个具有相同列的视图但我希望TransactAudit表中的'I'，'U'和'D'转换为Inserted，Updated和放置在名为vw_Audit_Data的视图中时，分别删除。因此，当视图显示已插入，已更新和已删除时，TransactAudt表会保留值I，U和D。

下面是我到目前为止尝试的代码，但它们都没有用。

我已经谷歌搜索了这个问题，并且对这种情况几乎没有建议。

我将不胜感激任何帮助。

提前谢谢。



我尝试了什么：

Please advise –
I have a basic audit table that tracks items that are ‘Inserted’, ‘Updated’ or ‘Deleted’ in a database table called TableM.
I have the triggers created to record the operation of someone inserting, updating or deleting a row and that record goes into a table called TransactAudit. The TransactAudit table has a column called ‘Operation’ that records an ‘I’ for Inserted items, a ‘U’ for updated items and a ‘D’ for deleted items.
I want to create a view that has the same columns but I want the ‘I’, ‘U’ and ‘D’ from the TransactAudit Table to be converted to "Inserted’, ‘Updated’ and ‘Deleted’ respectively when placed in the view called vw_Audit_Data. So the TransactAudt table retains the values ‘I’, ‘U’ and ‘D’ while the view shows ‘Inserted’, ‘Updated’ and ‘Deleted’.
Below is the code I have tried so far, but none of them work.
I have Google searched the heck out of this and found little to no advice for this situation.
I would appreciate any help on this.
Thank you in advance.

What I have tried:

--Here I am creating the basic view
CREATE VIEW [dbo].[vw_Audit_Data]
AS
SELECT        dbo.TransactAudit.*
FROM            dbo.TransactAudit

--Next I am trying to convert the values 
--of the "Operation" column as it is put
--into the view. 

--Tried a basic SET command
SET Operation = 'Inserted'
WHERE Operation = 'I';

SET Operation = 'Deleted'
WHERE Operation = 'D';

SET Operation = 'New Value'
WHERE Operation = 'U';



--Tried a basic WHEN command
WHEN [Operation] = 'I' THEN 'Inserted'
WHEN [Operation] = 'D' THEN 'Deleted'
WHEN [Operation] = 'U' THEN 'New Value'



--Tried a basic WHERE command
WHERE [Operation] = 'I' [Operation] = 'Inserted'
AND
WHERE [Operation] = 'D' [Operation] = 'Deleted'
AND
WHERE [Operation] = 'U' [Operation] = 'New Value'
--I also tried using 'OR', 'IF' and 'WHEN' in place 'AND'
--Nothing seems to work. 

GO

推荐答案

我这样做的方法是有一个单独的表格：

The way I would do it is to have a separate table:

ID   DescriptionENG
I    Inserted
D    Deleted
U    New Value

然后使用一个JOIN来组合数据：

Then use a JOIN to combine the data:

SELECT ta.ID, ... , tt.DescriptionENG FROM TransactionAudit ta
JOIN TransactionTypes tt ON ta.Operation = tt.ID

你好试试这个，



创建视图[ dbo]。[vw_Audit_Data]

AS

SELECT *，案例当[操作] ='我'然后'插入'

当[操作] ] ='D'那么'已删除'

当[操作] ='U'然后'新价值'

结束为[AliasName]

FROM [dbo]。[TransactAudit]

Hello Try this,

CREATE VIEW [dbo].[vw_Audit_Data]
AS
SELECT *,CASE WHEN [Operation] ='I' THEN 'Inserted'
WHEN [Operation] = 'D' THEN 'Deleted'
WHEN [Operation] = 'U' THEN 'New Value'
END AS [AliasName]
FROM [dbo].[TransactAudit]

这篇关于将列值更改为SQL Server中的可读值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！