Merging of rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Merging of rows

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-13 : 05:18:58

Dear friends,

I want a select query to get one row for each id and corresponding created on createdby modifiedon modifiedby deletedon deletedby

SELECT ID, M_ID, T_Name, T_Desc, Sch_Time, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, DeletedOn, DeletedBy
FROM #Schedule_Audit WHERE ID = 2

Note : Modified by and on might be multiple times so i need to get latest.
created and deleted data will be once.

sample data is as below


CREATE TABLE #Schedule_Audit
(
        ID INT NOT NULL, 
        M_ID INT NOT NULL, 
        T_Name varchar(100), 
        T_Desc varchar(100), 
        Sch_Time varchar(8), 
        CreatedBy varchar(200), 
        CreatedOn datetime, 
        ModifiedBy varchar(200), 
        ModifiedOn datetime, 
        DeletedBy varchar(200), 
		DeletedOn datetime, 
		Action char(1)
)

--created
INSERT INTO #Schedule_Audit(ID, M_ID, T_Name, T_Desc, Sch_Time, CreatedOn, CreatedBy, Action )
SELECT 1, 1, 'test1', 'testdesc', '08:00', '2010-04-09 11:30:00.000', 'abc@xyz.com', 'I'
UNION ALL
SELECT 2, 1, 'test2', 'testdesc', '08:00', '2010-04-09 12:30:00.000', 'abc@xyz.com', 'I'
UNION ALL
SELECT 3, 1, 'test3', 'testdesc', '08:00', '2010-04-09 13:30:00.000', 'abc@xyz.com', 'I'
UNION ALL
SELECT 4, 1, 'test4', 'testdesc', '08:00', '2010-04-09 14:30:00.000', 'abc@xyz.com', 'I'

--Modified
INSERT INTO #Schedule_Audit(ID, M_ID, T_Name, T_Desc, Sch_Time, ModifiedOn, ModifiedBy, Action )
SELECT 1, 1, 'test1', 'testdesc', '08:00', '2010-04-10 08:30:00.000', 'abc@xyz.com', 'U'
UNION ALL
SELECT 2, 1, 'test2', 'testdesc', '08:00', '2010-04-10 09:30:00.000', 'abc@xyz.com', 'U'
UNION ALL
SELECT 2, 1, 'test2', 'testdesc', '08:00', '2010-04-11 10:30:00.000', 'abc@xyz.com', 'U'
UNION ALL
SELECT 3, 1, 'test3', 'testdesc', '08:00', '2010-04-10 08:30:00.000', 'abc@xyz.com', 'U'

--Deleted
INSERT INTO #Schedule_Audit(ID, M_ID, T_Name, T_Desc, Sch_Time, DeletedOn, DeletedBy, Action )
SELECT 1, 1, 'test1', 'testdesc', '08:00', '2010-04-12 11:30:00.000', 'abc@xyz.com', 'D'
UNION ALL
SELECT 2, 1, 'test2', 'testdesc', '08:00', '2010-04-12 12:30:00.000', 'abc@xyz.com', 'D'
UNION ALL
SELECT 4, 1, 'test4', 'testdesc', '08:00', '2010-04-12 14:30:00.000', 'abc@xyz.com', 'D'


--SELECT * FROM #Schedule_Audit WHERE ID = 2

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-13 : 05:26:09

You can try like below

SELECT Top 1 ID, M_ID, T_Name, T_Desc, Sch_Time, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, DeletedOn, DeletedBy
FROM #Schedule_Audit WHERE ID = 2

Solutions are easy. Understanding the problem, now, that's the hard part

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-13 : 05:32:16

Yes you are right Understanding the problem hard part

Problem is that for the action 'I' There will not be any modified on and deletedon
and for action u there will not be any created on and deleted on.
and for action d there will not be any createdon and modifiedon.

I have to get all three in one row means i have to replace null value with the value from another rows.
I hope you understood... if not clear let me know...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 05:43:12

[code]
SELECT top 1 ID, M_ID, T_Name, T_Desc, Sch_Time,
(
select MAX(modifiedby) from #Schedule_Audit S1 where S1.ID=S2.ID group by ID
)as modifiedby ,
(

select MAX(ModifiedOn) from #Schedule_Audit S1 where S1.ID=S2.ID group by ID
) as ModifiedOn,
(

select MAX(DeletedBy) from #Schedule_Audit S1 where S1.ID=S2.ID group by ID
)
as ModifiedOn,
(

select MAX(DeletedOn) from #Schedule_Audit S1 where S1.ID=S2.ID group by ID
)as DeletedOn ,Action
FROM #Schedule_Audit S2 WHERE ID = 2
[/code]

PBUH

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-13 : 05:46:13

SELECT I.ID ,
Max(I.M_ID) M_ID,
Max(I.T_Name) T_Name,
Max(I.T_Desc) T_Desc,
Max(I.Sch_Time) Sch_Time,
Max(I.CreatedBy) CreatedBy,
Max(I.CreatedOn) CreatedOn,
Max(U.ModifiedBy) ModifiedBy,
Max(U.ModifiedOn) ModifiedOn,
Max(D.DeletedBy)DeletedBy,
Max(D.DeletedOn) DeletedOn,
Max(I.Action) Action FROM #Schedule_Audit I
Inner Join #Schedule_Audit U On I.ID = U.ID And U.ModifiedBy IS NOT NULL And U.ModifiedOn IS NOT NULL
Inner Join #Schedule_Audit D On I.ID = D.ID And D.DeletedBy IS NOT NULL And D.DeletedOn IS NOT NULL

WHERE I.ID = 2 And I.CreatedBy IS NOT NULL And I.CreatedOn IS NOT NULL
Group By I.ID

Solutions are easy. Understanding the problem, now, that's the hard part

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-13 : 05:54:25

quote:

Originally posted by Idera


SELECT top 1 ID, M_ID, T_Name, T_Desc, Sch_Time,
(
		select MAX(modifiedby) from #Schedule_Audit S1 where S1.ID=S2.ID group by ID
)as modifiedby ,
(

		select MAX(ModifiedOn) from #Schedule_Audit S1 where S1.ID=S2.ID group by ID
) as ModifiedOn,
(

		select MAX(DeletedBy) from #Schedule_Audit S1 where S1.ID=S2.ID group by ID
)
as ModifiedOn,
(

		select MAX(DeletedOn) from #Schedule_Audit S1 where S1.ID=S2.ID group by ID
)as DeletedOn ,Action
 FROM #Schedule_Audit S2 WHERE ID = 2

PBUH

Its hard for me to understand max ( modifiedby ).

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-13 : 06:06:50

quote:
Originally posted by ganeshkumar08

SELECT I.ID ,
Max(I.M_ID) M_ID,
Max(I.T_Name) T_Name,
Max(I.T_Desc) T_Desc,
Max(I.Sch_Time) Sch_Time,
Max(I.CreatedBy) CreatedBy,
Max(I.CreatedOn) CreatedOn,
Max(U.ModifiedBy) ModifiedBy,
Max(U.ModifiedOn) ModifiedOn,
Max(D.DeletedBy)DeletedBy,
Max(D.DeletedOn) DeletedOn,
Max(I.Action) Action FROM #Schedule_Audit I
Inner Join #Schedule_Audit U On I.ID = U.ID And U.ModifiedBy IS NOT NULL And U.ModifiedOn IS NOT NULL
Inner Join #Schedule_Audit D On I.ID = D.ID And D.DeletedBy IS NOT NULL And D.DeletedOn IS NOT NULL

WHERE I.ID = 2 And I.CreatedBy IS NOT NULL And I.CreatedOn IS NOT NULL
Group By I.ID

Solutions are easy. Understanding the problem, now, that's the hard part

Your query is giving me the output but i am affraid that max of all might give me wrong output and its looking illogical to have max with everything...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources