UNPIVOT with Table Value Constructor


Here is a sample to compare function UNPIVOT with Table Value Constructor in SQL Server 2008 and above.

CREATE TABLE Dates (id INT IDENTITY(1, 1),date1 DATETIME,date2 DATETIME,date3 DATETIME)

INSERT INTO Dates
(date1,date2,date3)
VALUES ('1/1/2012','1/2/2012','1/3/2012'),
('1/1/2012',NULL,'1/13/2012'),
('1/1/2012','1/2/2012',NULL),
('8/30/2012','9/10/2012','1/1/2013')

--Table Value
Select ID, MyDate, Date123
FROM Dates A
Cross Apply ( Values (Date1,'Date1'), (Date2,'Date2'), (Date3,'Date3')) B(MyDate, Date123)

--UNPIVOT
SELECT ID, MyDate ,date123 FROM (SELECT id,date1,date2, date3 FROM Dates) src
UNPIVOT (MyDate
FOR date123 IN ([date1],
[date2],
[date3])) unpvt


Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s