SQL Query – How to get data in Hierarchical Structure?

Download sample data with Schema

Prepare an SQL query to display the given data in “Hierarchical Structure” based on the level of the parent node. In a way that tree structure can be prepared.

I have one table:

• Location – It contains the data with Parent – Child relationship.

The structure of the table and result set is as follows:

Location

Resultant Data

Please provide your SQL query in comment of this article.

Click here to download the sample data including schema.

Following are the solutions provided by:
Me (Chirag Vidani)

WITH LocationList (LocationId,LocationName,ParentLocationId,LocationLevel)
AS
(
    SELECT loc.Id, loc.Name,loc.ParentId, 1 AS LocationLevel
    FROM Location AS loc
    WHERE loc.ParentId IS NULL
    UNION ALL
    SELECT l.Id,l.Name,l.ParentId,list.LocationLevel + 1
    FROM Location AS l
    INNER JOIN LocationList AS list
        ON l.ParentId = list.LocationId
)
SELECT LocationId,LocationName,isnull(ParentLocationId,0) 'ParentLocationId',LocationLevel FROM LocationList

Mr. Kaushik Patel

WITH LocationLevelReports (LocationID, Locationname , ParentLocationId, LocationLevel)
AS
(
-- Anchor member definition
SELECT L.ID, L.name, ISNULL(L.ParentId,0) ParentId, 
1 AS LocationLevel
FROM Location AS L
WHERE ParentId IS NULL
UNION ALL
-- Recursive member definition
SELECT L.ID, L.name, L.ParentId, 
LocationLevel + 1 as LocationLevel
FROM Location AS L
INNER JOIN LocationLevelReports AS R
ON L.ParentId = R.LocationID

)
select * from LocationLevelReports

SQL Query - How to Select top 2 highest salaried employees from each department?

Download sample data with Schema

Let us share some knowledge of SQL Query.

I have three tables mentioned below:

• Employee – Master table
• SalaryDetails – Reference table with salary information
• Department – Master table

The sample data into these tables is show below:

Employee	Department
Salary Details

Now the SQL Query to be prepared should contain resultant data as ‘top 2 highest salaried employees from each department’ as below :

Resultant Data

Please provide your queries in comment of this article.

Schema with sample data is provided on download link, which will save your time for preparing sample tables and data. Click here to download.

Following are the solutions provided by:
Me (Chirag Vidani)

select 
 data.DepartmentName 'Department Name', data.EmployeeName 'Employee Name', data.Salary 'Salary/Day' 
from
 (select 
  d.Name 'DepartmentName',e.Name 'EmployeeName',sd.Salary 'Salary',dense_rank() over(partition by sd.DepartmentId order by sd.Salary desc)'SalaryRank' 
 from 
  Salarydetails sd
 join Department d 
  on sd.DepartmentId = d.Id
 join Employee e 
   on sd.EmployeeID =e.Id) as data
where 
 data.SalaryRank <= 2
order by 
 data.DepartmentName,data.Salary desc

Mr. Kaushik Patel

SELECT 
 d.* 
FROM
 (select 
  S.*,D.NAME DepartmentName,E.NAME EmployeeName ,RANK() OVER (PARTITION BY DeptID ORDER BY SALARY DESC) AS Raw_Rank 
 from 
  Salary s 
 inner join Employee e 
  on s.EmployeeId = e.id 
 inner join Department d 
  on s.DepartmentId = d.id ) d 
WHERE 
 Raw_Rank <=2

How to RESET identity column in SQL Server

During the development of any application we input dummy data and which is stored in database. But frequently we come to the point where we want all records of the table to be deleted and also want to start the identity columns values from 0.

For this we delete the data using truncate command.
It will delete the data from table and also reset the identity column’s value to 0.

truncate table <table_name>

truncate table product

But the truncate command fails to delete the data if there is relationship given to the table and the identity column is not reset.

For this after firing the delete command execute below command.
It will reset the identity column of product table to 0.

DBCC CHECKIDENT('<table_name>', RESEED, <reset from number>)

DBCC CHECKIDENT('product', RESEED, 0)

Preparing “Data Dictionary” using SQL Query

Data dictionary is the required while documenting any project. So here is the query for preparing the data dictionary of any database.

SELECT [Table Name] = OBJECT_NAME(c.object_id)
,[Column Name] = c.name
,[Data Type] = t.name
,[Size] = c.max_length
,[Description] = isnull(ex.value,'') 
FROM sys.columns c 
LEFT OUTER JOIN sys.extended_properties ex ON (ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description') 
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id 
WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
AND OBJECT_NAME(c.object_id) in (select name from sys.tables where type= 'U') 
ORDER BY OBJECT_NAME(c.object_id), c.column_id

Just include the column of the table definition (if any other required - i.e. if you want to display whether the column is null/not null, just include the field from sys.objects table and so on).