Rescursive Sort Sql Server 2005. How to sort by name in a function (see example)

Hello,
I have this example. My question is, how can I sort after "sortcol" and
"empname" ?
I mean, I get with this function all the nodes sorted but behalft the nodes
I want to
sort to "empname". How can I do this?
WITH NodeTree2(empid, empname, mgrid, depth, sortcol)
AS
(
SELECT empid, empname, mgrid, 0, CAST(empid AS VARBINARY(900))
FROM employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.depth+1, CAST(sortcol + CAST(E.empid
AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE('| ', depth)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
Thanks for helping me!
AndreasAndreas,
Construct the binary sort path out of row numbers based on mgrid
partitioning and empname sorting:
WITH EmpCTE(empid, empname, mgrid, depth, sortcol)
AS
(
SELECT empid, empname, mgrid, 0, CAST(1 AS VARBINARY(MAX))
FROM employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.depth+1,
sortcol + CAST(
ROW_NUMBER() OVER(PARTITION BY E.mgrid ORDER BY E.empname)
AS BINARY(4))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE('| ', depth)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Andreas Klemt" <aklemt68@.hotmail.com> wrote in message
news:%23xtB3ROiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this example. My question is, how can I sort after "sortcol" and
> "empname" ?
> I mean, I get with this function all the nodes sorted but behalft the
> nodes I want to
> sort to "empname". How can I do this?
> WITH NodeTree2(empid, empname, mgrid, depth, sortcol)
> AS
> (
> SELECT empid, empname, mgrid, 0, CAST(empid AS VARBINARY(900))
> FROM employees
> WHERE empid = 1
> UNION ALL
> SELECT E.empid, E.empname, E.mgrid, M.depth+1, CAST(sortcol + CAST(E.empid
> AS BINARY(4)) AS VARBINARY(900))
> FROM Employees AS E
> JOIN EmpCTE AS M
> ON E.mgrid = M.empid
> )
> SELECT
> REPLICATE('| ', depth)
> + '(' + (CAST(empid AS VARCHAR(10))) + ') '
> + empname AS empname
> FROM EmpCTE
> ORDER BY sortcol
> Thanks for helping me!
> Andreas
>

Representing a graph in TSQL

Hi!
I need to represent a graph in TSQL.
The graph is directed. It is essentially a number of trees which all have various (non-root) nodes in common with each other.
Any ideas or pointers would be most welcome :)
Thanks,
AngelosWithout knowing what your instructor covered in class, we can't even give you a good guess at how to do this. All three of the classic methods for representing graphs are easy to support in SQL, and many of the more avante-guard representations are easy too.

Talk with the instructor. It is still quite early in the session (at least most places). They should be willing to help you work this out.

-PatP

Reposting: Expand All / Collapse All feature

1. Is there a way to Expand All nodes or Collapse All Nodes in a drill-down
report designed with RS ? I find it annoying to click every + sign to open
all nodes. Maybe, there is an easier way that I do not know.
2. When the same drill-down report is partially expanded and I export the
report as-it-is to Excel, the exported file has only the partial
data.....only the data that was expanded. The other data is never exported.
What can I do to resolve this ?
Please respond.Yes, you can do this.
Step 1) Create a textbox outside of your table/matrix. Give it a name, e.g.
Toggler. Step 2) Highlight the row you want to hide/show. Set its Visibility
Hidden property to False, and use the textbox you created at Step 1 as the
ToggleItem property.
Charles Kangai, MCT, MCDBA
"DC" wrote:
> 1. Is there a way to Expand All nodes or Collapse All Nodes in a drill-down
> report designed with RS ? I find it annoying to click every + sign to open
> all nodes. Maybe, there is an easier way that I do not know.
> 2. When the same drill-down report is partially expanded and I export the
> report as-it-is to Excel, the exported file has only the partial
> data.....only the data that was expanded. The other data is never exported.
> What can I do to resolve this ?
> Please respond.
>
>|||Also, if you put the visibility settings at Group line level (i.e.
highlight the whole group by clicking on the left hand tab) rather cell
level, when you export to Excel you'll get full outlining/grouping for
ALL data regardless of how the report is currently 'expanded'. (This
only works with SP1).
Regards
Chris
Charles Kangai wrote:
> Yes, you can do this.
> Step 1) Create a textbox outside of your table/matrix. Give it a
> name, e.g. Toggler. Step 2) Highlight the row you want to hide/show.
> Set its Visibility Hidden property to False, and use the textbox you
> created at Step 1 as the ToggleItem property.
> Charles Kangai, MCT, MCDBA
> "DC" wrote:
> > 1. Is there a way to Expand All nodes or Collapse All Nodes in a
> > drill-down report designed with RS ? I find it annoying to click
> > every + sign to open all nodes. Maybe, there is an easier way that
> > I do not know.
> >
> > 2. When the same drill-down report is partially expanded and I
> > export the report as-it-is to Excel, the exported file has only the
> > partial data.....only the data that was expanded. The other data
> > is never exported. What can I do to resolve this ?
> >
> > Please respond.
> >
> >
> >
> >|||Here is an expand all that I am using:
Create Report parameter: "ExpandAll"
Available Values:
Label: Yes Value: True
Label: No Value: False
Default Value: No
Group1: =Fields!Status.Value
Initial Appearance Expression: =IIF( Parameters!ExpandAll.Value = true,
true, false)
Group2 =Fields!Name.Value
Initial Appearance Expression: =IIF( Parameters!ExpandAll.Value = true,
true, false)
Visibility:
Hidden expression: =IIF( Parameters!ExpandAll.Value = true, False, True)
ToggleItem: Status
Detail records:
Visibility:
Hidden expression: =IIF( Parameters!ExpandAll.Value = true, false, true)
ToggleItem: Name
Good luck,
Chuck Kee
"Chris McGuigan" wrote:
> Also, if you put the visibility settings at Group line level (i.e.
> highlight the whole group by clicking on the left hand tab) rather cell
> level, when you export to Excel you'll get full outlining/grouping for
> ALL data regardless of how the report is currently 'expanded'. (This
> only works with SP1).
> Regards
> Chris
> Charles Kangai wrote:
> > Yes, you can do this.
> > Step 1) Create a textbox outside of your table/matrix. Give it a
> > name, e.g. Toggler. Step 2) Highlight the row you want to hide/show.
> > Set its Visibility Hidden property to False, and use the textbox you
> > created at Step 1 as the ToggleItem property.
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "DC" wrote:
> >
> > > 1. Is there a way to Expand All nodes or Collapse All Nodes in a
> > > drill-down report designed with RS ? I find it annoying to click
> > > every + sign to open all nodes. Maybe, there is an easier way that
> > > I do not know.
> > >
> > > 2. When the same drill-down report is partially expanded and I
> > > export the report as-it-is to Excel, the exported file has only the
> > > partial data.....only the data that was expanded. The other data
> > > is never exported. What can I do to resolve this ?
> > >
> > > Please respond.
> > >
> > >
> > >
> > >
>