?
Solved

CTE, SQL Server, FOR XML

Posted on 2014-02-20
6
Medium Priority
?
852 Views
Last Modified: 2014-02-21
I am trying to prepare some data for use with the command FOR XML EXPLICIT, I started by investigating this at Link and it seems simple enough.  I started to implement a nested CTE.  The code is below.

WITH Divi AS
(
      SELECT 0 AS 'Parent', di.DivisionID, di.DivisionNo,NULL AS 'DeptId', di.DivisionName, Null AS 'DepartmentNumber', CAST(NULL AS Varchar(50)) AS 'DepartmentName' from Division di
      UNION ALL
      SELECT dp.DivisionID AS 'Parent',NULL 'DivisionID' , NULL AS 'DivisionNo', dp.DeptId, Null AS 'DivisionName', dp.DepartmentNumber, dp.DepartmentName FROM Divi div  JOIN Department dp ON div.DivisionID = dp.DivisionID
)

SELECT * FROM Divi

It does the job of getting the data as one might expect.  The problem is I am unsure how to order it so that it would conform to the ordering seen in the link.
0
Comment
Question by:Alyanto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39874144
You will have to add an element of hierarchy and use it to sort. This example might help:

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39876238
Unfortunately this does not answer my question but it does progress another area of the problem.  I would ultimately like the nesting of data to be output using the FOR XML EXPLICIT option.  This said the parent child relationships here need to be in order to ensure that the XML is in the correct order.
Sample XML
   <division name="One" id=1>
       <Department name="A" Id=1/>
       <Department name="B" Id=2/>
   </division>
<division name="Two" id=2>
       <Department name="C" Id=3/>
       <Department name="D" Id=4/>
   </division>

Sample table as expected
Parent    DivisionId   DepartmentId   DivisionName   DepartmentName    
NULL      1                 NULL                 One                   NULL
1             NULL          1                        NULL                 A
1             NULL          2                        NULL                 B
NULL      2                 NULL                 Two                   NULL
2             NULL          3                        NULL                 C
2             NULL          4                        NULL                 D

The noteable thing is the output order and the nesting is related.  Parent is an alias of Division Id found in the department table.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39876449
So you order by Parent, DepartmentId.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 1

Author Comment

by:Alyanto
ID: 39876555
Ordering was one of the first things I tried unfortunately the result is that all of the NULL values appear at the top as a group.  The rest do sort themselves as you might expect and want.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 39876562
Then sort by:

...
ORDER BY
       ISNULL(Parent,DivisionId),
       DepartmentId

Open in new window

0
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 39877166
Cheers and thanks for you help, a perfect solution.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question