How to arrange table data in parent child format in SQL server

Question

0.00/5 (No votes)

See more:

how to query this table

SQL

txnno       Name		type	isHeadr	headr
    1000	BEFORE	        JO	Y	
    1001	WHILE		JO	Y	
    1002	data aaa	JO	N	1000
    1003	bbbbdfds	JO	N	1000
    1004	ccccccc	        JO	N	1000
    1012	aaa111		JO	N	1001
    1015	bbb222		JO	N	1001
    1016	ccc333		JO	N	1001

to get this result in MY SQL SERVER

SQL

txnno	Name		type	isHeadr	headr
    1000	BEFORE	        JO	Y		
    1002	data aaa	JO	N	1000
    1003	bbbbdfds	JO	N	1000
    1004	ccccccc	        JO	N	1000
    1001	WHILE		JO	Y
    1012	aaa111		JO	N	1001
    1015	bbb222		JO	N	1001
    1016	ccc333		JO	N	1001

need to arrange the table to parent child format

What I have tried:

i tried to join the same table

Posted 14-Aug-16 22:41pm

sneha john andr

Updated 15-Aug-16 2:20am

v4

Add a Solution

Comments

Maciej Los 15-Aug-16 6:21am

You have to use ORDER BY instruction.

W∴ Balboos, GHB 15-Aug-16 6:37am

You source (first) table is ordered by the first column [txnno].
You target table doesn't seem to be ordered by anything - could you please explain what it is that gives that particular order to your table?

For what you want, all I can envision is a brute-force methodology that would be tedious with the eight entries you have and ludicrous for a table of any real size.<br>

1 solution

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)

sneha john andr · Accepted Answer · 2016-08-15T02:20:00

Solution 1

I got the solution

SQL

SELECT *
FROM YourTable
ORDER BY ISNULL(headr,txnno) ASC, txnno

Posted 15-Aug-16 2:20am

sneha john andr