SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 I need help in formulating a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pssheba
Yak Posting Veteran

95 Posts

Posted - 07/24/2012 :  10:16:01  Show Profile  Reply with Quote
Hi everyone
This is my database:

key1 (varchar(2)) key2 (varchar(2)) text
_______________ _______________ ____
1 NULL abc
2 NULL efg
3 1 reply(abc)
4 2 reply(efg)
5 1 reply(abc)
6 NULL hij
7 5 reply2(abc)
I need to display the above lines in the following order:

key1 (varchar(2)) key2 (varchar(2)) text
_______________ _______________ ____
1 NULL abc
3 1 reply(abc)
5 1 reply(abc)
7 5 reply2(abc)
2 NULL efg
4 2 reply(efg)
6 NULL hij

The ratio behind the order stands like:
A key2 value: NULL means: first line in a group ("main line").
All lines with key2=key1 of "Main line" ("second degree") should be placed beneath the "main line" (reply).
Lines with key2=key1 of "second degree" should be placed beneath "second degree" and so on.
This is the order posts in a form are placed.
Can anyone formulate a query to run upon the db shown above so that the result will look like the secon list?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/24/2012 :  11:09:47  Show Profile  Reply with Quote

;With CTE
AS
(
SELECT key1,key2,[text],CAST(key1 as varchar(max)) as [path],CAST(1 AS int) AS level
FROM table
WHERE key2 IS NULL
UNION ALL
SELECT t.key1,t.key2,t.[text],CAST(c.[path] + '\' + CAST(t.key1 AS varchar(max)) AS varchar(max)),c.Level + 1
FROM table t
INNER JOIN CTE c
ON c.key1 = t.key2
)

SELECT key1,key2,[text]
FROM CTE
ORDER BY LEFT([path],CHARINDEX('\',[path] + '\')-1),Level


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pssheba
Yak Posting Veteran

95 Posts

Posted - 07/24/2012 :  13:32:29  Show Profile  Reply with Quote
Thanks a lot!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/24/2012 :  14:16:50  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000