Using Common Table Expression

Many projects store the hierarchical data into database. This hierarchical data is used to generate the tree view on UI like folder structure in the explorer, location hierarchy and company hierarchy in account software.

Generally data is stored in the database in a one table. Let’s take an example so that we can understand better. Let’s say we need to create the location hierarchy in form of tree view on screen. It will look like this

Asia (1)
- India (3)
- Mumbai (7)
- Delhi (8)
- Calcutta (9)
- Bangalore (10)
- Japan (4)
- Tokyo (11)
- Nagasaki (12)
- Hiroshima (13)
Europe (2)
- England (5)
- London (14)
- Manchester (15)
- France (6)
- Paris (16)

Here we assumed that data in brackets are their Locationid. So our database table will look like below and u can also create in u r sample database.

CREATE TABLE Locations
(
LocationID int primary key not null,
ParentLocationID int null,
LocationName varchar(50) not null
)

Insert the values …..

INSERT Into Locations Values (1, null, 'Asia')
INSERT Into Locations Values (2, null, 'Europe')
INSERT Into Locations Values (3, 1, 'India')
INSERT Into Locations Values (4, 1, 'Japan')
INSERT Into Locations Values (5, 2, 'England')
INSERT Into Locations Values (6, 2, 'France')
INSERT Into Locations Values (7, 3, 'Mumbai')
INSERT Into Locations Values (8, 3, 'Delhi')
INSERT Into Locations Values (9, 3, 'Calcutta')
INSERT Into Locations Values (10, 3, 'Bangalore')
INSERT Into Locations Values (11, 4, 'Tokyo')
INSERT Into Locations Values (12, 4, 'Nagasaki')
INSERT Into Locations Values (13, 4, 'Hiroshima')
INSERT Into Locations Values (14, 5, 'London')
INSERT Into Locations Values (15, 5, 'Manchester')
INSERT Into Locations Values (16, 6, 'Paris')

To get the above mentioned o/p we can use Recursive Stored Procedure. But if u write recursive stored procedure it supports only 32 levels. And u might land up with the following error….

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Hence there is another way to get rid of this explained below…

CREATE PROCEDURE GetLocationTree_OneQuery
@LocationID int = 1
AS
BEGIN
WITH LocationTree(LocationID, ParentLocationID, LocationName, Levels)
AS
(
-- anchor member
SELECT LocationID, ParentLocationID, LocationName, 1
FROM Locations
WHERE LocationId = @LocationID

UNION ALL

-- recursive member
SELECT
L.LocationID, L.ParentLocationID, L.LocationName, LocationTree.Levels + 1
FROM Locations L
INNER JOIN LocationTree
ON LocationTree.LocationID = L.ParentLocationID
)

SELECT * From LocationTree
END

If look at carefully this stored procdedure has a 2 queries one with createting a temporay table for use and fireing recursive trigger internally and the last second one just gives the o/p table.

“With” statement creates the temp table “LocationTree” for use with the columns that we can define. Using this table we can fire the recursive query with the original table.
It has also limited of recursion up to 100 levels………. But u in real world u might not required too many levels




Recursive Stored Procedure

CREATE PROCEDURE GetLocations
@LocationID int = 1
AS
BEGIN
Create Table #NestedTree(
LocationID int,
ParentLocationID int,
LocationName varchar(50),
LocationLevel int
)

INSERT into #NestedTree SELECT LocationID, ParentLocationID,
LocationName, 1
FROM Locations where LocationID = @LocationID

EXEC GetLocationTree @LocationID, 1

SELECT
LocationID,
ParentLocationID,
LocationName,
LocationLevel
FROM #NestedTree

DROP TABLE #NestedTree

END

GO

CREATE PROCEDURE GetLocationTree
@LocationID int = 1,
@Level int = 0
AS
BEGIN

SET NOCOUNT ON
DECLARE @NextLevel int, @PLocationID int, @LocationName VARCHAR(50)

-- Build a cursor to loop through all the kids of this post
DECLARE c1 CURSOR LOCAL
FOR SELECT LocationID, LocationName FROM Locations
WHERE ParentLocationID = @LocationID

SET @PLocationID = @LocationID

SET @Level = @Level + 1

OPEN c1

FETCH NEXT FROM c1 INTO @LocationID, @LocationName

WHILE @@FETCH_STATUS = 0

BEGIN
-- Put this record in the temp table
INSERT #NestedTree (LocationID,
ParentLocationID,
LocationName,
LocationLevel)
VALUES (@LocationID, @PLocationID, @LocationName, @Level)

-- Process all the children for this post
EXEC GetLocationTree @LocationID, @Level

-- And get the next record at this level
FETCH NEXT FROM c1 INTO @LocationID, @LocationName
END

CLOSE c1
DEALLOCATE c1
SET NOCOUNT OFF
END

/////// ************************************* ///////

Run the Stored Procedure query in the Query Analyzer

GetLocations 1
This will give you Asia and the location below that with the levels also if the levels are here limited to three but if you think of unlimited levels this recursive procedure will fail since it only supports up to 32 levels and u might land up with following error.

Msg 217, Level 16, State 1, Procedure GetLocationTree
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


That means u cannot call stored procedure under the stored procedure after 32 levels. So what is other solution to get rid of this? Definitely Microsoft has answer to this also.

Use of PIVOT & UNPIVOT in SQL Server 2005

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.