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 upload/download file to/from server using WebClient method…?

Today’s business applications are working on client server architecture and on timely basis files are being uploaded to server from client or vice versa.

In ASP.Net we can upload a file to server using File-Upload control. But what-if we want to upload a file automatically or programmatically to the server using Windows application, Windows Service or Web application?

The possible ways to upload a file to server are follows:

• FTP Upload 
• HTTP Upload
• ...

Here I will show how to upload/download a file using HTTP method (i.e. commonly known as Web Client method).

In Web Client method we require a virtual directory to which file is to be upload under domain name (i.e. http://www.YourDomainName.com/ClientFiles/). So we are going to upload files to “ClientFiles” directory.

Note: You need to install and configure WebDAV (Web Distributed Authoring and Versioning) in IIS. Click here for an article on Installing and Configuring WebDAV on IIS 7.

Code to upload a file on Server using WebClient method:

System.Net.WebClient webClient = new System.Net.WebClient();
string sourceFilePath = @"D:\MyDocuments\DataFile.xml" ;
string webAddress = "http://www.YourDomainName.com/ClientFiles/";
string destinationFilePath= webAddress + "DataFile.xml";
webClient.Credentials = new System.Net.NetworkCredential("username", "password", "domain");
webClient.UploadFile(destinationFilePath, "PUT", sourceFilePath);
webClient.Dispose();

Code to download a file from Server using WebClient method:

System.Net.WebClient webClient = new System.Net.WebClient();
string webAddress = "http://www.YourDomainName.com/ClientFiles/";
string sourceFilePath = webAddress + "DataFile.xml";
string destinationFilePath = @"D:\MyDocuments\DataFile.xml";
webClient.Credentials = new System.Net.NetworkCredential("username", "password", "domain");
webClient.DownloadFile(sourceFilePath, destinationFilePath);
webClient.Dispose();

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)