How to make SQL query to get parent child data in group

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hello,

I have one customer table, and have parent and sub customer in same table with "ParentId" field relation. Table as below.

SQL

CustId      CustName    ParentId
1           Cust1       0 
2           Cust2       0
3           Sub1Cust1   1
4           Cust3       0
5           Sub2Cust1   1
6           Sub1Cust2   2
7           Sub2Cust2   2
8           Sub3Cust    1
9           Sub1Cust3   4
10          Sub4Cust1   1

What I want is from MS SQL Query so it keep parent and child record together, Like output as below:

SQL

CustId      CustName    ParentId
1           Cust1        0 
3           Sub1Cust1    1
5           Sub2Cust1    1
8           Sub3Cust1    1
10          Sub4Cust1    1
2           Cust2        0
6           Sub1Cust2    2
7           Sub2Cust2    2
4           Cust3        0
9           Sub1Cust3    4

Can anybody please give me a hint how to do it with single query?

Thanks in advance

What I have tried:

I am trying to use the

SQL

SELECT	ROW_NUMBER() OVER(PARTITION BY [ParentId] ORDER BY  [CustomerName] ASC) AS [R],[CustomerId],[ParentId],[CustomerName] FROM	[Customer] Order by [R],[ParentId]

But not getting how to exactly use that

Posted 27-Apr-18 9:19am

npdev13

Updated 27-Apr-18 20:24pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2018-04-27T10:52:00

One way is to utilize Common Table Expression. Using a hierarchical query include the topmost parent id in the result and use that as a primary sorting field.

For example something like

SQL

WITH 
CustCTE (CustId, CustName, ParentId, UltimateParent) AS (
   SELECT t1.CustId, 
          t1.CustName, 
          t1.ParentId, 
          t1.CustId
   FROM  Customer t1
   WHERE ParentId = 0
   UNION ALL 
   SELECT t2.CustId, 
          t2.CustName, 
          t2.ParentId, 
          cte.UltimateParent
   FROM CustCTE  cte,
        Customer t2
   WHERE t2.ParentId = cte.CustId
)
SELECT cte.CustId, 
       cte.CustName, 
       cte.ParentId
FROM CustCTE cte
ORDER BY cte.UltimateParent, 
         cte.ParentId

npdev13 · Answer 2 · 2018-04-27T20:25:00

Solution 2

I found the solution

ORDER BY CASE WHEN ParentId = 0 THEN CustID ELSE ParentId END ASC
, CASE WHEN ParentId = 0 THEN '0' ELSE CustName END ASC

Posted 27-Apr-18 20:25pm