Combine columns from other row to derive new colum

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 2008 Forums

Transact-SQL (2008)

Combine columns from other row to derive new colum

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2014-10-30 : 15:18:12

I need to pick value from a second row and populate the value in first row. so if I consider first column as n, then datevalue of n+1 would be populated for n in a new column.

Below is a sample data, I have multiple datasets similar to this in a table



CREATE TABLE #temp (idnumber bigint, aid uniqueidentifier,cid uniqueidentifier,dtcreate datetime)

INSERT INTO #temp
SELECT 1000013337,'30DD885F-AF3E-4B5A-801B-E38EF43EEBA8','E2F40991-D86B-4735-96A2-01C77C142C3C','10/8/2014 11:35:56' UNION ALL
SELECT 1000013209,'59ECA44D-41A9-4D81-84B9-758DA2B857A8','E2F40991-D86B-4735-96A2-01C77C142C3C','10/8/2014 9:00:48' UNION ALL
SELECT 1000013200,'59ECA44D-41A9-4D81-84B9-758DA2B857A8','E2F40991-D86B-4735-96A2-01C77C142C3C','10/8/2014 8:55:16' UNION ALL
SELECT 1000013354,'84C602DA-2193-4EAF-BD0C-621E6AF90EB3','E2F40991-D86B-4735-96A2-01C77C142C3C','10/8/2014 12:01:23' UNION ALL
SELECT 1000013407,'84C602DA-2193-4EAF-BD0C-621E6AF90EB3','E2F40991-D86B-4735-96A2-01C77C142C3C','10/8/2014 12:37:19' UNION ALL
SELECT 1000013350,'84C602DA-2193-4EAF-BD0C-621E6AF90EB3','E2F40991-D86B-4735-96A2-01C77C142C3C','10/8/2014 11:54:21'

I trying to get a select which will return below result.


idnumber|aid|cid|dtstart|dtend
1000013200|59ECA44D-41A9-4D81-84B9-758DA2B857A8|E2F40991-D86B-4735-96A2-01C77C142C3C|2014-10-08 08:55:16.000|2014-10-08 09:00:48.000

In the above case, dtcreate of n+1 record became the dtend for n record.

This is only for 1 cid, there are multiple cid's inbetween these records for ease i just picked up one of them.

Any help is appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources