September 13, 2011 at 5:59 am
The query that is given below will display the result set in the attached txt File. I want to display the result in a different way for the reporting purpose that is also given in attached txt File.
I have implemented CDC in SQL SERVER 2008R2, But I want to display result set in a different way.
Thanks
SELECT TOP 1000 [__$start_lsn]
,[__$end_lsn]
,[__$seqval]
,case [__$operation]
when 2 then 'Insert'
WHEN 3 THEN 'BeforeUpdate'
WHEN 4 THEN 'AfterUpdate'
WHEN 1 THEN 'Delete' END AS [Action]
,[__$operation]
,[__$update_mask]
,[ID]
,[Name]
FROM [TestDB].[cdc].[dbo_CDCTesting_CT]
go
September 13, 2011 at 6:41 am
From my point of view the expected output doesn't match the original data:
In the original data there's one update capture missing where Test changed to Test1. Also, ActionTime is not part of the original data and is misleading if there's only a date part presented.
In general, formatting stuff like that should be done at the presentation layer.
If you need to do it using T-SQL, follow at least the basic principle that a row itself holds all the attributes of an entity and the position of the row within a return set doesn't matter (= doesn't change the context).
So, instead of having a separate row to indicate the action described add it to each row and eliminate the "grouping header".
What you would need to do is to perform a CrossTab (or PIVOT) to get the Before and After values into separate columns. Then you'd need to either UNION (or UNPIVOT) the data by Id and Name.
September 14, 2011 at 1:03 am
Yes,
I will correct data, but my require ment is like that, I can explain further here in new attatched xls file.
pl see attached file.
Hopefully this will explain.
In case of Update ther will be 2 rows in a table and in case of both insert and delete there will be only one row table.
I used pivot for this but in pivot i dont need compute functions like sum, without this it was not working.
Thanks for helping me.
September 14, 2011 at 1:12 am
azhar.iqbal499 (9/14/2011)
Yes,I will correct data, but my require ment is like that, I can explain further here. This is just dummy Data
Col1 Col2 Action AcctionTime
Vall1 val1 insert 14/09/2011 11:29
Val1 Val1 BeforeUpdate 14/09/2011 11:29
Val2 Val2 AfterUpdate 14/09/2011 11:29
Val VAl Delate 15/09/2011 11:29
I want result as
Action ActionTime ColName BeforeUpdate AfterUpdate
Insert 14/09/2011 11:29 Col1 Vall1
Insert 14/09/2011 11:29 Col2 Vall1
Update 14/09/2011 11:29 Col1 Val1 Vall2
Update 14/09/2011 11:29 Col2 Val1 Vall2
Delete 15/09/2011 11:29 Col1 Val2
Delete 15/09/2011 11:29 Col2 Val2
Hopefully this will explain.
In case of Update ther will be 2 rows in a table and in case of both insert and delete there will be only one row table.
I used pivot for this but in pivot i dont need compute functions like sum, without this it was not working.
Thanks for helping me.
September 14, 2011 at 10:02 am
Unfortunately, the dummy data are still not valid.
But here's an example of what you might look for (as a side note: please notice how I present the sample data in a ready to use format without any Excel sheet...)
DECLARE @tbl TABLE
(
Col1 CHAR(4),
Col2 CHAR(4),
Action_ VARCHAR(20),
ActionTime datetime
)
INSERT INTO @tbl
VALUES
('Val1','xal1','insert','20110914 11:29'),
('Val1','xal1','BeforeUpdate','20110914 11:29'),
('Val2','xal2','AfterUpdate','20110914 11:29'),
('Val','VAl','Delete','20110915 11:29');
WITH cte AS -- step 1: get the Before and After values into separate columns
(
SELECT
'col1' AS colname,
col1 AS colvalue,
action_,
ActionTime
FROM @tbl
UNION ALL
SELECT
'col2' AS colname,
col2 AS colvalue,
action_,
ActionTime
FROM @tbl
)
-- step 2: pivot the data using the CrossTab method
SELECT
CASE action_
WHEN 'AfterUpdate' THEN 'Update'
WHEN 'BeforeUpdate' THEN 'Update'
ELSE action_
END AS action_grp,
ActionTime,
colname,
MAX(CASE WHEN action_ IN ('Delete','BeforeUpdate') THEN colvalue ELSE '' END) AS BeforeUpdate,
MAX(CASE WHEN action_ IN ('insert','AfterUpdate') THEN colvalue ELSE '' END) AS AfterUpdate
FROM cte
GROUP BY
CASE action_
WHEN 'AfterUpdate' THEN 'Update'
WHEN 'BeforeUpdate' THEN 'Update'
ELSE action_
END,
ActionTime,colname
ORDER BY ActionTime
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply