Need help with TSQL

Question

Need help with TSQL

rajg

SSChasing Mays

Points: 631
More actions
August 7, 2013 at 6:37 am

#297382

Hi,
I have table structure as shown in the below image. I have attached script for the table with sample data.
There is parent child relation between Time_Id and Parent_Id columns.
I want output rows in the following order. Basically all the root nodes(with NULL parent_id) should be in the order of SortOrder column. But at the same time if root has childs, all the childs should immediately appear below the root in the sorted order.
Can you please help ?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Since the sortorder column isn't particularly useful, why don't you populate it with the sort order that you actually want?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rajg SSChasing Mays Points: 631 More actions · Answer 2

I am really not sure but this is that table structure with data that is given to me. Would really be a great help if someone can help. I already tried recursive CTE's, using ROW_NUMBERs etc but was not able to get the required output.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Is this what you're after?

WITH Source AS (

SELECT [TIME_ID], , [DESCRIPTION], [PARENT_ID], CAST(1000+ROW_NUMBER() OVER(ORDER BY [SORTORDER]) AS VARCHAR(10)) AS [SORTORDER]

FROM tblTime),

Recur AS (

SELECT [TIME_ID], , [DESCRIPTION], [PARENT_ID], CAST([SORTORDER] AS VARCHAR(1000)) AS [SORTORDER]

FROM Source

WHERE [PARENT_ID] IS NULL

UNION ALL

SELECT t.[TIME_ID], t., t.[DESCRIPTION], t.[PARENT_ID], CAST(r.[SORTORDER] + '/' + t.[SORTORDER] AS VARCHAR(1000))

FROM Source t

INNER JOIN Recur r ON r.[TIME_ID] = t.[PARENT_ID])

SELECT [TIME_ID], , [DESCRIPTION], [PARENT_ID]

FROM Recur

ORDER BY [SORTORDER];

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

rajg (8/7/2013)
I am really not sure but this is that table structure with data that is given to me. Would really be a great help if someone can help. I already tried recursive CTE's, using ROW_NUMBERs etc but was not able to get the required output.

I mean, just for the sample data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rajg SSChasing Mays Points: 631 More actions · Answer 5

This is perfect...Thankkkkk you very much !! Dont know why I spent so much of time on this 🙂

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

rajg (8/7/2013)
This is perfect...Thankkkkk you very much !! Dont know why I spent so much of time on this 🙂

You're welcome.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

rajg SSChasing Mays Points: 631 More actions · Answer 7

I had tried number of ways with row number and CTE...but never thought of adding '/' between parent and child sortorder field...I was trying addition, simple concatenation.

Could you please help me to understand how that '/' helps to get the required data ?

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 8

You don't actually need the '/', that's just for clarity to show the sort order for each part of the hierarchy. The sort order value just has to be fixed width - four characters in this case (1001 onwards).

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537