SQL Server : Using CTE(Common Table Expression) fetch a Tree View

Goal

Using CTE show a complete tree view.

Challenge

When a developer tries to search CTE in internet, there are millions of articles which already exists which says to get a tree view using CTE. But there is a problem with most of them. They dont give correct output. For making the data show in correct way users should enter data in an order, else it wont come. So first I will explain how the examples on internet mislead.

---

SQL - Create temp table

-- DECLARE
DECLARE @Company AS TABLE(EmpID INT, ParentID INT, PersonName VARCHAR(100));

-- Insert Temp Records
INSERT INTO @Company(EmpID, ParentID, PersonName) 
VALUES(1 , NULL , 'Maulik Dhorajia')
    , (2 , NULL , 'Bhavesh Gohel')
    , (3 , NULL , 'Dinesh Padhiyar')
    , (4 , 2 , 'Vijay Kumar')
    , (5 , 1 , 'Jitendra Makwana')
    , (6 , 4 , 'Jayesh Dhobi')
    , (7 , 1 , 'Shivpalsinh Jhala')
    , (8 , 5 , 'Amit Patel')
    , (9 , 3 , 'Abidali Suthar')

-- Default data

SELECT * FROM @Company;

---

SQL - Which usually misleads to get correct tree view

-- Incorrect result which we usually find on Internet
;WITH CTECompany
AS
(
    SELECT EmpID, ParentID, PersonName , 0 AS HLevel 
    FROM @Company
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT C.EmpID, C.ParentID, C.PersonName , (CTE.HLevel + 1) AS HLevel 
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Misleading SQL
SELECT * FROM
(
    SELECT 
        EmpID
        , ParentID
        , HLevel
        , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
    FROM CTECompany
) AS P
ORDER BY HLevel;

---

SQL - Correct CTE with Tree View

-- Working Example
;WITH CTECompany
AS
(
    SELECT 
        EmpID, 
        ParentID, 
        PersonName , 
        0 AS HLevel,
        CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT 
        C.EmpID, 
        C.ParentID, 
        C.PersonName , 
        (CTE.HLevel + 1) AS HLevel,
        CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Working Example
SELECT 
    EmpID
    , ParentID
    , HLevel
    , PersonName
    , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
FROM CTECompany
ORDER BY OrderByField,PersonName;

Hope this helped and saved a lot of your time!

Reply for the comment "Nice try. still relies on order of data entry. add (44 , 1 , 'XXitendra Makwana')". I tried to see what was wrong but I can see the correct tree with the same query. Not sure what is wrong. Can anyone else see the issue and post a comment? Thanks in advance.

SQL Server : Sample for reading xml data

Goal

Simple SQL query to fetch data from an xml.

--//---------- Declare ----------//
DECLARE @xmlSample XML;

SET @xmlSample = '<GUIDs>
  <GUID Temp="a">1</GUID>
  <GUID Temp="a">2</GUID>
  <GUID Temp="a">3</GUID>
  <GUID Temp="b">4</GUID>
  <GUID Temp="b">5</GUID>
</GUIDs>';

--//---------- Set Query ----------//
SELECT * FROM 
(
    SELECT 
        d.value('.', 'INT') AS GUID , 
        d.value('@Temp', 'VARCHAR(36)') AS Temp
    FROM @xmlSample.nodes('/GUIDs/GUID') d(d) 
) AS A

Change the path of TempDB in SQL Server 2005

Error : System databases master, model, msdb, and tempdb cannot be detached.

use master

go

Alter database tempdb modify file (name = tempdev, filename = 'D:\SQL Database 2005\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'D:\SQL Database 2005\templog.ldf')

go

Read XML in SQL Server 2005/2008

Can read an xml file format in SQL Server 2005 2005/2008

To read the attributes use "@AttributeName"

To read the node under it use "NodeName[0]"

Just copy/patse the below given stuff in sql server and run it.

--//---------- Declare ----------//

DECLARE @xmlTest XML    

SET @xmlTest = '<GUIDs>

  <GUID Temp="a">1</GUID>

  <GUID Temp="a">2</GUID>

  <GUID Temp="a">3</GUID>

  <GUID Temp="b">4</GUID>

  <GUID Temp="b">5</GUID>

</GUIDs>'



--//---------- Set Query ----------//

SELECT Temp , MAX(GUID) FROM (

SELECT xmlTest.value('.', 'INT') AS GUID , xmlTest.value('@Temp', 'VARCHAR(36)') AS Temp

FROM @xmlTest.nodes('/GUIDs/GUID') d(xmlTest) ) AS A

GROUP BY Temp

SQL 2005/2008 Query to retrieve a comma seperated single row from a table.

This query will help to combine all records in a single row result with comma seperation. I have taken both type string as well as integers.

-- Take a table variable

DECLARE @TBL as TABLE(VAL1 INT, VAL2 VARCHAR(10));

-- POPULATE THE VALUES

INSERT INTO @TBL VALUES(10,'A');

INSERT INTO @TBL VALUES(20,'B');

INSERT INTO @TBL VALUES(30,'C');

INSERT INTO @TBL VALUES(40,'D');

INSERT INTO @TBL VALUES(50,'E');

-- Show the values in tables

SELECT * FROM @TBL;



-- Declare variables

DECLARE @CommaValsInt AS VARCHAR(MAX);

DECLARE @CommaValsString AS VARCHAR(MAX);



-- Gets the sql query with Comma Seperated

SELECT 

    @CommaValsInt = COALESCE(@CommaValsInt + ',', '') + CAST(VAL1 AS VARCHAR(10)) 

    , @CommaValsString = COALESCE(@CommaValsString + ''',''', '''') + VAL2

FROM 

    @TBL;

-- Show the result

SELECT @CommaValsInt AS ResultForInt , (@CommaValsString + '''') ResultForString

Calculate Standard Deviation Using SQL Server

SQL Procedure to get the Standard Deviation

Hello everyone, here is the formaula to calculate the standard deviation.

---

-- Create a temp table insert some rows as raw data.

DECLARE @tbl AS TABLE(ID INT IDENTITY(1,1) , Val DECIMAL(18,4));

INSERT INTO @tbl VALUES (10.20 );

INSERT INTO @tbl VALUES (99.27 );

INSERT INTO @tbl VALUES (23.56 );

INSERT INTO @tbl VALUES (56.92 );

INSERT INTO @tbl VALUES (02.37 );

SELECT * FROM @tbl;



-- Declare variables @AVG , @Count.

-- @AVG is the average of the "Val".

-- @Count is the number of records in the table.

DECLARE @AVG DECIMAL(18,4), @Count INT;

SELECT @AVG = AVG(VAL) , @Count = COUNT(ID) FROM @tbl;

SELECT @AVG AS Average , @Count AS RecordCount;



-- Declare the sum of Deviation

-- FORMULA = Single value is substracted by the average. The square of the result and sum of all the values will get "@SumDev".

DECLARE @SumDev DECIMAL(18,4);

SELECT @SumDev = SUM( SQUARE(VAL - @AVG) ) AS SumSquareDeviation FROM @tbl;

SELECT @SumDev;



-- Get the final stanard Deviation

-- FORMULA = Gets the last @SumDev divide it by a number less than the total number of rows(@Count - 1). Get the quareroot and get the Standard Deviation

SELECT SQRT((@SumDev /(@Count-1))) AS StandardDeviation;

---