Most of the time when you came across queries which need to convert the columns into rows and rows into columns. it was not possible with the SQL server 2000 but now you can use the PIVOT and UNPIVOT relational operators to manipulate a table-valued expression into another table in SQL SERVER 2005. A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data.
here i have given some examples that to achieve this task and to understand better those who have not worked on the PIVOT & UNPIVOT.
PIVOT function :- Here i have taken an example of project and related tasks and no of persons assigned to each task of project.
1. Create a table tblResource
CREATE TABLE [dbo].[tblResource](
[Project] [varchar](10) NULL,
[Task] [varchar](10) NULL,
[Persons] [int] NULL
) ON [PRIMARY]
2. Insert data into it. this data will contain how many person working for given task on that project.
INSERT INTO [tblResource] VALUES ('Proj1', 'Task1', 3)
INSERT INTO [tblResource] VALUES ('Proj1', 'Task2', 2)
INSERT INTO [tblResource] VALUES ('Proj1', 'Task3', 4)
INSERT INTO [tblResource] VALUES ('Proj1', 'Task4', 1)
INSERT INTO [tblResource] VALUES ('Proj1', 'Task5', 3)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task1', 4)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task2', 2)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task3', 3)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task4', 5)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task5', 7)
3. we want o/p as shown below.
ProjectName Task1 Task2 Task3 Task4 Task5
Proj1 3 2 4 1 3
Proj2 4 2 3 5 7
to get above o/p we can write query ---------------
SELECT Project AS ProjectName, Task1, Task2 ,Task3 , Task4, Task5
FROM
(SELECT Project, Task, Persons
FROM tblResource) Table1
PIVOT
(SUM(Persons) FOR Task IN
(Task1, Task2 ,Task3 , Task4, Task5)
)AS Table2
This means that the unique values returned by the Task column themselves become fields in the final result set. As a result, there is a column for each Task specified in the pivot clause — in this case they are Task1, Task2, Task3, Task4, Task5. The Persons column serves as the value column, against which the columns returned in the final output, called the grouping columns, are grouped i.e(Table1) . In this case, the grouping columns are aggregated by the SUM function. Table1 should return the unique values.
UNPIVOT function :- UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows.before doing any thing create a another table named [tblResource1] where as per the stucture of the above output.
1. Create Table tblResource1
CREATE TABLE [dbo].[tblResource1](
[ProjectName] [varchar](10) NULL,
[Task1] [int] NULL,
[Task2] [int] NULL,
[Task3] [int] NULL,
[Task4] [int] NULL,
[Task5] [int] NULL
) ON [PRIMARY]
2. insert data into it
INSERT INTO [tblResource1] VALUES ('Proj1',3,2,4,1,3)
INSERT INTO [tblResource1] VALUES ('Proj2',4,2,3,5,7)
3. we want a o/p as shown below.
Project TASK Persons
-------- --------- ---------
Proj1 Task1 3
Proj1 Task2 2
Proj1 Task3 4
Proj1 Task4 1
Proj1 Task5 3
Proj2 Task1 4
Proj2 Task2 2
Proj2 Task3 3
Proj2 Task4 5
Proj2 Task5 7
to get above o/p we can write query ---------------
SELECT ProjectName AS Project, CAST(Task AS VARCHAR) TASK, Persons
FROM
(SELECT ProjectName, Task1, Task2, Task3, Task4, Task5
FROM tblResource1) Table1
UNPIVOT
(Persons FOR Task IN
(Task1, Task2, Task3, Task4, Task5)
)AS Table2
This means that you must identify two additional columns. The column that will contain the column values you are rotating (Task1,Task2,...) will be called Task, and the column that will hold the values that currently reside under the columns being rotated will be called Persons. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition.
Note that UNPIVOT is not the exact reverse of PIVOT. PIVOT performs an aggregation and hence merges possible multiple rows into a single row in the output.
