Solved

SQL Statement

Posted on 2014-03-14
4
276 Views
Last Modified: 2014-03-18
Hi

I need to Select som data in a SQL db something like in the attached file.

My table and fields :

PROJTABLE.NUMBER_
PROJTABLE.PARENTPROJNUMBER
PROJTABLE.NAME
PROJTABLE.Value1
ProjH.jpg
0
Comment
Question by:conceptdata
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928755
please check out this code
;with data as (
 select p.NUMBER_, p.PARENTPROJNUMBER, p.NAME, p.Value1
  from yourtable p
where p.PARENTPROJNUMBER IS NULL  --- presuming this are the "root parents", or the one you are looking for ...

 UNION ALL -- here is the recursive stuff

 select p.NUMBER_, p.PARENTPROJNUMBER, p.NAME, p.Value1
   from DATA d
   JOIN yourtable p  
      ON p.PARENTPROJNUMBER = d.NUMBER_
    AND d.PARENTPROJNUMBER <> d.NUMBER_
)
select * from data 

Open in new window

0
 

Author Comment

by:conceptdata
ID: 39929029
Hi Guy

Works nice, but is it possible to sort the output like in the attached file.

Like this :

942800 -
942930 - 942800
942840 - 942800
942842 - 942840

and, output the niveua of the hierachy ?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39929089
the output is normally sorted like that.
to add the level, change like this
;with data as (
 select p.NUMBER_, p.PARENTPROJNUMBER, p.NAME, p.Value1 , 0 as HierarchyLevel
  from yourtable p
where p.PARENTPROJNUMBER IS NULL  --- presuming this are the "root parents", or the one you are looking for ...

 UNION ALL -- here is the recursive stuff

 select p.NUMBER_, p.PARENTPROJNUMBER, p.NAME, p.Value1, d.HierarchyLevel + 1
   from DATA d
   JOIN yourtable p  
      ON p.PARENTPROJNUMBER = d.NUMBER_
    AND d.PARENTPROJNUMBER <> d.NUMBER_
)
select * from data 

Open in new window

0
 

Author Comment

by:conceptdata
ID: 39938050
Hi Again Guy - Thanks for the good answer.

I would like to use the statement in report builder 3.0 and get a report like the attached one.
And get it illustrated at diagrams in the hierachy.

Is it something you help me with too. ??


I can create a new question for that . !
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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