Hi All ,
Thanks for your replies .Sorry if haven't followed the forum rules , please forgive me . I kept only images as they help to understand the scenario
better . Now here i am posting the DDL scripts and also the expected output with some more columns.( I have used table variables here)
DECLARE @Tab TABLE (
RwNum INT,
JobParentID UNIQUEIDENTIFIER,JobSource NVARCHAR(MAX),
PackageType NVARCHAR(MAX),
UpdateType NVARCHAR(MAX),
IsAutoPassed NVARCHAR(256),
IsCanceled NVARCHAR(256),
IsSkipped NVARCHAR(256),
Result NVARCHAR(256),
Fired NVARCHAR(256),
RuleName NVARCHAR(256)
)
INSERT INTO @Tab
SELECT 1,'7ca42851-c3d2-42da-b5b9-d40392ae24fb','PreSubmissionSource','Xap', 'FullUpdate','','','FALSE','Pass','FALSE','RuleValidationResult^XboxLive'
UNION
SELECT 2,'7ca42851-c3d2-42da-b5b9-d40392ae24fb','PostSubmission','Xap', 'PartialUpdate','FALSE','','TRUE','Failed','FALSE','RuleValidationResult^GameCategoryNameChange'
UNION
SELECT 3,'7ca42851-c3d2-42da-b5b9-d40392ae24fb','PreSubmissionSource','Xap', '','TRUE','TRUE','','Pass','FALSE','RuleValidationResult^XboxLive'
UNION
SELECT 4,'7ca42851-c3d2-42da-b5b9-d40392ae24fb','PreSubmissionSource','Xap', '','TRUE','TRUE','','Pass','FALSE','RuleValidationResult^CumulativeDownload'
UNION
SELECT 5,'7ca42851-c3d2-42da-b5b9-d40392ae24fb','PostSubmission','Xap', 'PartialUpdate','FALSE','','TRUE','Failed','FALSE','PreinstalledPackage'
UNION
SELECT 6,'7ca42851-c3d2-42da-b5b9-d40392ae24fb','PreSubmissionSource','Xap', 'PartialUpdate','TRUE','TRUE','','Pass','FALSE','RuleValidationResult^CumulativeDownload'
UNION
SELECT 7,'7ca42851-c3d2-42da-b5b9-d40392ae24fb','PreSubmissionSource','Xap', 'FullUpdate','','','','Pass','','RuleValidationResult^XboxLive'
UNION
SELECT 1,'2004235d-af05-4e29-ab8d-50b80a088dd4','PreSubmissionSource','Xap', 'FullUpdate','TRUE','TRUE','','Pass','FALSE','RuleValidationResult^CumulativeDownload'
UNION
SELECT 2,'2004235d-af05-4e29-ab8d-50b80a088dd4','PreSubmissionSource','Xap', 'FullUpdate','TRUE','','FALSE','','FALSE','RuleValidationResult^CumulativeDownload'
SELECT * FROM @Tab ORDER BY 2 DESC ,1 ASC
Original source table doesn't RwNum column . In order for me to iterate row by row on the above table i have created "RwNum" column . This column
has unique ID for every row of a JobIDParent. RwNum column is generated as below
ROW_NUMBER() OVER( ORDER BY JobIDParent) AS RwNum,
The output should be below :
DECLARE @Output TABLE (
RwNum INT,
JobParentID UNIQUEIDENTIFIER,JobSource NVARCHAR(MAX),
PackageType NVARCHAR(MAX),
UpdateType NVARCHAR(MAX),
IsAutoPassed NVARCHAR(256),
IsCanceled NVARCHAR(256),
IsSkipped NVARCHAR(256),
Result NVARCHAR(256),
Fired NVARCHAR(256),
RuleName NVARCHAR(256)
)
INSERT INTO @Output
SELECT 1,'7ca42851-c3d2-42da-b5b9-d40392ae24fb', 'PreSubmissionSource', 'Xap', 'FullUpdate','TRUE','TRUE','FALSE','Pass','FALSE','RuleValidationResult^XboxLive'
UNION
SELECT 2,'7ca42851-c3d2-42da-b5b9-d40392ae24fb', 'PostSubmission', 'Xap', 'PartialUpdate','FALSE','','TRUE','Failed','FALSE','RuleValidationResult^GameCategoryNameChange'
UNION
SELECT 5,'7ca42851-c3d2-42da-b5b9-d40392ae24fb', 'PostSubmission', 'Xap', 'PartialUpdate','FALSE','','TRUE','Failed','FALSE','PreinstalledPackage'
UNION
SELECT 4,'7ca42851-c3d2-42da-b5b9-d40392ae24fb', 'PreSubmissionSource', 'Xap', 'PartialUpdate','TRUE','TRUE','','Pass','FALSE','RuleValidationResult^CumulativeDownload'
UNION
SELECT 1,'2004235d-af05-4e29-ab8d-50b80a088dd4', 'PreSubmissionSource', 'Xap', 'FullUpdate','TRUE','TRUE','FALSE','Pass','FALSE','RuleValidationResult^CumulativeDownload'
SELECT * FROM @Output ORDER BY 2 DESC ,1 ASC
Merge rules to generate the above Output :
All the below rules should
be satisfied to merge two rows
1) We only need to merge the data that is related to the same JobIDParent
2) Data in column for any two merging rows should not conflict .
I.e. Data in a column of a merging row should not be different to the data in the same column for the other merging row(only if both rows are having not NULL value in
that column )
3) we can merge two rows ,only if the data in all the columns satisfy any of the below cases
case i) If
the data in the column of a row is having null/EmptySpace & the data in the same column for the other row is a valid value i.e. non empty or not null
case ii) Data
in a column of a row is equal to the data in the same column of the other row .
Output analysis by applying above rules:
In the @Output analysis for JobIDParent '7CA42851-C3D2-42DA-B5B9-D40392AE24FB'
i)RwNum =1 is formed as a result of merge using the above rules from RwNum 3 & RwNum 7 .First the RowNum 1
and 3 are merged and the output of these two rows are merged with RwNum 7
ii) RwNum =2 is not merged with any of the rows in fact table , as the no row in the table satisfy all the merge
rules with RwNum =2
iii) RwNum = 4 is formed as a result of merge using the above rules from RwNum 6 .
iv) RwNum =5 is not merged with any of the rows in fact table , as the no row in the table satisfy all the
merge rules with RwNum =5
In the @Output analysis for JobIDParent '7CA42851-C3D2-42DA-B5B9-D40392AE24FB'
i)RwNum =1 is formed as a result of merge using the above rules from RwNum 2.
In this way we want the rows to be merged on the table
Sorry if my earlier post is not understandable .
Thanks in advance.