How to use Pivot function in SQL server

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

How to use Pivot function in SQL server

Author

Topic

PUTTU PATIL
Starting Member

7 Posts

Posted - 2014-02-11 : 04:36:37

I have table as shown below

Date1 Date2 Date3
2013-02-01 2013-03-01 2013-04-01

I want this to be in vertically as
shown below

Date
2013-02-01
2013-03-01
2013-04-01

PUTTU PATIL

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2014-02-11 : 10:27:00

[code]CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO[/code]

here's the link : http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

--------------------------
Joins are what RDBMS's do for a living

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-02-12 : 04:30:35

[code]
declare @table table(Date1 date, Date2 date, Date3 date)
insert into @table select '2013-02-01','2013-03-01','2013-04-01'

select urs_column from @table
cross apply(select Date1 union all select Date2 union all select Date3)upvt(urs_column)
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 05:15:47

if above 2008 you can use this too


select dt from @table
cross apply(values(date1),(date2),(date3))t(dt)

see
http://visakhm.blogspot.com/2012/05/multifacet-values-clause.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources