Solved

Display Hierarchy In Column Format

Posted on 2006-06-15
7
587 Views
Last Modified: 2008-03-04
Greetings,

I am using this hierarchy function listed below and query to display my hierarchy in this table called ticket_category_tmp.
 for sql server 2000:
create function hirearchy(@id as int) returns varchar(1000)
as
begin
declare @tree as varchar(1000)
set @tree = ''
if (parent_cat_code <> 0 and  parent_cat_code is not  null )

select @tree = dbo.hirearchy(parent_Cat_code) +'>' + description from ticket_category_tmp where id = @id

return @tree
end
go  

SELECT     ticket_category.id, dbo.hirearchy(ticket_category.id)
FROM         ticket_category
ORDER BY dbo.hirearchy(ticket_category.id)

The Results of this query display like this:

ID         Tree
===  =================
7       Admin>
15     Admin>HardWare
16     Admin> HardWare>MBOARD
17     Admin>NETWORK
18     Admin>NETWORK>OUTAGE
19     Admin>NETWORK>OUTAGE>VPN

I would like to have this display by columns instead of rows. Any solutions for this? The first colum would be the root or parent then the next column would be level 1, level 2, level 3, etc......

0
Comment
Question by:Omega002
  • 4
  • 2
7 Comments
 

Author Comment

by:Omega002
ID: 16912805
Also could you do this using cases?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16912814
SQL 2005 does use the PIVOT function to do that...OnSQL 2000 you will probably have to do some scripting for that...
0
 

Author Comment

by:Omega002
ID: 16912927
Can you show me how you would do it?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Omega002
ID: 16912938
Can you show me how you would use it in SQL 2005 using the pivot table?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16919287
Check the following link for more info...

http://msdn2.microsoft.com/en-us/library/ms177410.aspx

Hope this helps...
0
 

Author Comment

by:Omega002
ID: 16919690
The example from the link is count number values so how would I apply this to displaying hierarchy data?
SELECT     ticket_category.id, dbo.hirearchy(ticket_category.id)
FROM         ticket_category
ORDER BY dbo.hirearchy(ticket_category.id)

The Results of this query display like this:

ID         Tree
===  =================
7       Admin>
15     Admin>HardWare
16     Admin> HardWare>MBOARD
17     Admin>NETWORK
18     Admin>NETWORK>OUTAGE
19     Admin>NETWORK>OUTAGE>VPN

This is what I am looking for:
ID    Level 1      Level 2      Level 3
=     ======  =====    ======
7       Admin      HardWare MBOARD
 Can you show me how you would approach this?
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 500 total points
ID: 16929451
it is very hard to acheive what you want because the levels are dynamic.
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
sqlquerystress - To test db performance 8 42
SQL Help 27 45
VBScript Write Column Headers 3 38
T-SQL Default value in Select? 5 27
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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