I need help in formulating a query

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)

I need help in formulating a query

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2012-07-24 : 10:16:01

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

52326 Posts

Posted - 2012-07-24 : 11:09:47

[code]
;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
[/code]

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

pssheba
Yak Posting Veteran

95 Posts

Posted - 2012-07-24 : 13:32:29

Thanks a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 14:16:50

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources