Solved

How do I create a tree diagram from non-recursive data in SQL Server 2008 R2

Posted on 2011-03-16
4
628 Views
Last Modified: 2012-05-11
I have a question and it's really frustrating and I think it's simply that I don't completely understand how to set up hierarchies in SQL Server 2008 R2. I have looked at recursive CTEs, the hierarchy data type and XML and really can't get my head around how I can do what I need to do.
 
I need to be able to create a tree structure from data in an SQL Server 2008 R2 database  which will be sent as HTML in an auto-generated email which would be similar to the following:
 
Author
-- Year book written
------ Publisher
----------- Genre
 
So that for each author I can provide a tree of all their information so they could see what books they have written in a given time period e.g.
 
John Doe
-- 1999
------ Apress
----------- Historical
----------- Science Fiction
------ Wiley
----------- Classical
----------- Historical
----------- Science Fiction
-- 2009
------ Bloomsbury
----------- Classical
----------- Romance
----------- Science Fiction
------ Wiley
----------- Classical
 
I actually need to generate HTML (I added "ul" and "li" as part of the select statement using FOR XML PATH, which sort of added the tags correctly although not quite according to XHTML standards, but I can tweak that once the hierarchy itself is sorted) to present the information in an email and had used FOR XML PATH but that didn't work exactly as I needed but I think part of the problem was that I really didn't understand how to generate the hierarchy in the first place. I can see how to do it with recursive data but my data is not recursive, although I don't know if I can apply the same principles even if the data is not recursive.
 
I get the information from a temporary variable I created that has a row for each full hierarchy - I used a temporary variable so that the code looks nice and is easy to understand and strips out any data I don't need.  The table variable is re-used in a couple of places in the stored procedure, hence part of the reason for using a table variable.  These are examples of the rows with the columns seperated by dashes below:
 
John Doe - 1999 - Apress - Historical
John Doe - 1999 - Apress - Science Fiction
John Doe - 1999 - Wiley - Classical
John Doe - 1999 - Wiley - Historical
John Doe - 1999 - Wiley - Science Fiction
John Doe - 2009 - Bloomsbury - Classical
John Doe - 2009 - Bloomsbury - Romance
John Doe - 2009 - Bloomsbury - Science Fiction
John Doe - 2009 - Wiley - Classical
 
This may not be the best way to capture the data for generating the email hierarchy but it gave me a list of all the various possibilities, which is why I started there and if I used excel pivot tables the data in this column format was able to give me exactly what I needed.  But I don't want to use excel, I want the emails to auto generate.

Any insight is gratefully received.

Thank you.
0
Comment
Question by:lilycollins
  • 3
4 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 35153782
Here is what I got:

select Author.Author as [is],
       [Year].[Year] as [is], 
       Publisher.Publisher as [is], 
       Genre.Genre as [is]
  from YourTable Genre
  inner join (select distinct Author from YourTable) Author on Author.Author = Genre.Author
  inner join (select distinct Author, [Year] from YourTable) [Year] on [Year].Author = Genre.Author and [Year].[Year] = Genre.[Year]
  inner join (select distinct Publisher from YourTable) Publisher on Publisher.Publisher = Genre.Publisher
FOR XML AUTO

Open in new window


Which generates the following XML:

<Author is="John Doe">
	<Year is="1999">
		<Publisher is="Apress">
			<Genre is="Historical"/>
			<Genre is="Science Fiction"/>
		</Publisher>
		<Publisher is="Wiley">
			<Genre is="Classical"/>
			<Genre is="Historical"/>
			<Genre is="Science Fiction"/>
		</Publisher>
	</Year>
	<Year is="2009">
		<Publisher is="Bloomsbury">
			<Genre is="Classical"/>
			<Genre is="Romance"/>
			<Genre is="Science Fiction"/>
		</Publisher>
		<Publisher is="Wiley">
			<Genre is="Classical"/>
		</Publisher>
	</Year>
</Author>

Open in new window


It's not XHTML, but I think you can get there through simple string replaces.
0
 

Author Comment

by:lilycollins
ID: 35153860
Thank you very much for your response, I completely agree that the conversion to XHTML is a straightforward replace etc.

I will try your solution and let you know how it works out.

Thank you.
0
 

Author Closing Comment

by:lilycollins
ID: 35157647
Very helpful and clearly documented response.
0
 

Author Comment

by:lilycollins
ID: 35157696
Dear wdosanjos,

Thank you for your help.  I had been pulling my hair out over this and as per usual it's a pretty simply solution once you have seen it, smile.

It works beautifully.

Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Log Backup 2 21
SQL Server 2008 R2 - stored prodecure with execution plan make faster 24 115
Update in Sql 7 30
TSQL previous 5 23
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now