data ordering problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

data ordering problem

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-06-02 : 05:38:01

Hi,
I have category and its subcategory combined single table.

Its output is like
CatId ParentCatId CatName
1 0 test1
2 0 test2
3 0 test3
4 1 test1_1
5 1 test1_2
6 3 test3_1

I need to sort above records with below out put with its parent category

CatId ParentCatId CatName
1 0 test1
4 1 test1_1
5 1 test1_2
2 0 test2
3 0 test3
6 3 test3_1

how can i sort or any thing else plz help

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 05:51:04

I am assumming that the CatName if is a child has the parent name to it as a prefix.If thats the case then this will work

select * from yourtable order by Code,parentcatid

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-06-02 : 05:59:26

no its not a case.. catname can be any thing

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-06-02 : 06:03:25

data can be of any type.. may i need to apply cte or looping or any other idea for this sorting..

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-02 : 06:38:08

CTE is the best option
you will get the required result with Levels.
Let me know if you require the CTE query?

Lets unLearn

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-06-02 : 07:00:24

quote:
Originally posted by naveengopinathasari

CTE is the best option
you will get the required result with Levels.
Let me know if you require the CTE query?

Lets unLearn

Yes, can i have its cte ?

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 07:27:27

I dont think there is a need for a CTE.Just try this and see


select * from yourtable order by case when parentid=0 then catid else parentid end

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-06-02 : 08:08:18

quote:
Originally posted by Idera

I dont think there is a need for a CTE.Just try this and see

select * from yourtable order by case when parentid=0 then catid else parentid end

PBUH

yes idera thanking you

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 08:56:01

quote:
Originally posted by keyursoni85

quote:
Originally posted by Idera

I dont think there is a need for a CTE.Just try this and see

select * from yourtable order by case when parentid=0 then catid else parentid end

PBUH

yes idera thanking you

You are welcome.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources