Solved

SQL Statement

Posted on 2014-03-14
4
284 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

805 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