Converting a column into Row - 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

General SQL Server Forums

New to SQL Server Programming

Converting a column into Row

Author

Topic

Shubhanshu
Starting Member

8 Posts

Posted - 2012-02-28 : 09:02:37

Hi
Need help ,i have a cloumn of dates in give format
____DueDate______
2012-02-23
2012-02-24
2012-02-25

Now I want in below mentioned format
Orignal Date Due date
2012-02-23 2012-02-24
2012-02-24 2012-02-25

Thanks In advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 10:00:24

try this?



DECLARE @sample TABLE (
	[DueDate] DATE
	)
	
INSERT @sample
VALUES ('20120223'),('20120224'),('20120225'),('20120229')

; WITH rankedDates AS (
	SELECT
		ROW_NUMBER() OVER ( ORDER BY [DueDate] ) AS [rnk]
		, [DueDate]
	FROM @sample
	)
SELECT
	rd.[DueDate] AS [Original Date]
	, rd2.[DueDate] AS [Due Date]
FROM
	rankedDates AS rd
	JOIN rankedDates AS rd2 ON rd2.[rnk] = rd.[rnk] + 1

Results:


Original Date Due Date
------------- ----------
2012-02-23    2012-02-24
2012-02-24    2012-02-25
2012-02-25    2012-02-29

Note -- I introduced a gap as an extra sample

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Shubhanshu
Starting Member

8 Posts

Posted - 2012-02-29 : 06:09:29

[quote]Originally posted by Transact Charlie

try this?



DECLARE @sample TABLE (
	[DueDate] DATE
	)
	
INSERT @sample
VALUES ('20120223'),('20120224'),('20120225'),('20120229')

; WITH rankedDates AS (
	SELECT
		ROW_NUMBER() OVER ( ORDER BY [DueDate] ) AS [rnk]
		, [DueDate]
	FROM @sample
	)
SELECT
	rd.[DueDate] AS [Original Date]
	, rd2.[DueDate] AS [Due Date]
FROM
	rankedDates AS rd
	JOIN rankedDates AS rd2 ON rd2.[rnk] = rd.[rnk] + 1

Results:


Original Date Due Date
------------- ----------
2012-02-23    2012-02-24
2012-02-24    2012-02-25
2012-02-25    2012-02-29

Note -- I introduced a gap as an extra sample

Charlie
[blue]===============================================================

It is working ..Thanks a lot Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources