Hierachy by TSQL

  • Hi all

    can anyone help my with a bit of TSQL please. I have a table with a list of codes that depend on a list of other codes. a code can be in either column, the dependent or the code column.

    Id like to create a list of which codes first to last.  The First level codes have no dependents, the second level only have dependents in the first level ............ the last level has dependents in any other level and are not dependents on any other code.

    🙂 i hope that makes sense. Heres an example table

    _Field _Dependent

    0010 0020

    0010 0030

    0010 0060

    0010 0070

    0010 0080

    0010 0090

    0010 0091

    0010 0092

    0010 0100

    0010 0110

    0010 0140

    0010 0430

    0010 0440

    0020 0030

    0020 0040

    0020 0050

    0020 0060

    0030 0040

    0030 0050

    0060 0040

    0060 0050

    0070 0080

    0070 0090

    0070 0091

    0070 0092

    0080 NULL

    0090 NULL

    0091 NULL

    0092 NULL

    0100 0110

    0100 0120

    0100 0130

    0100 0133

    0100 0136

    0100 0138

    0100 0140

    0100 0150

    0100 0160

    0100 0170

    0100 0180

    0100 0190

    0100 0200

    0100 0240

    0100 0340

    0100 0380

    0100 0385

    0100 0390

    0100 0425

    0100 0430

    0100 0440

    0110 0120

    0110 0130

    0110 0133

    0110 0136

    0110 0138

    0120 0130

    0120 0133

    0120 0136

    0120 0138

    0130 NULL

    0133 NULL

    0136 NULL

    0138 NULL

    0140 0150

    0140 0160

    0140 0170

    0140 0180

    0140 0190

    0140 0200

    0140 0210

    0140 0220

    0140 0230

    0140 0240

    0140 0250

    0140 0280

    0140 0310

    0140 0340

    0140 0350

    0140 0360

    0140 0370

    0140 0380

    0140 0385

    0140 0390

    0140 0400

    0140 0410

    0140 0420

    0140 0425

    0150 NULL

    0160 NULL

    0170 NULL

    0190 NULL

    0194 NULL

    0198 NULL

    0200 0210

    0200 0220

    0200 0221

    0200 0222

    0200 0230

    0200 0231

    0200 0232

    0210 0211

    0210 0212

    0211 NULL

    0212 NULL

    0220 0221

    0220 0222

    0221 NULL

    0230 0231

    0230 0232

    0231 NULL

    0232 NULL

    0240 0250

    0240 0260

    0240 0270

    0240 0280

    0240 0290

    0240 0300

    0240 0310

    0240 0320

    0240 0330

    0250 0260

    0250 0270

    0280 0290

    0280 0300

    0310 0320

    0310 0330

    0340 0350

    0340 0360

    0340 0370

    0390 0400

    0390 0410

    0390 0420

     

    thanks in advance

     

  • Please provide your sample data in the form of CREATE TABLE/INSERT statements.  Also provide your desired results,  based on the sample data.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • create table #Table1
    (
    _Field varchar(4)
    , _Dependency varchar(4)
    )
    go
    insert into #Table1
    select '0010', '0020'
    union select '0010', '0030'
    union select '0010', '0060'
    union select '0010', '0070'
    union select '0010', '0080'
    union select '0010', '0090'
    union select '0010', '0091'
    union select '0010', '0092'
    union select '0010', '0100'
    union select '0010', '0110'
    union select '0010', '0140'
    union select '0010', '0430'
    union select '0010', '0440'
    union select '0020', '0030'
    union select '0020', '0040'
    union select '0020', '0050'
    union select '0020', '0060'
    union select '0030', '0040'
    union select '0030', '0050'
    union select '0060', '0040'
    union select '0060', '0050'
    union select '0070', '0080'
    union select '0070', '0090'
    union select '0070', '0091'
    union select '0070', '0092'
    union select '0080', NULL
    union select '0090', NULL
    union select '0091', NULL
    union select '0092', NULL
    union select '0100', '0110'
    union select '0100', '0120'
    union select '0100', '0130'
    union select '0100', '0133'
    union select '0100', '0136'
    union select '0100', '0138'
    union select '0100', '0140'
    union select '0100', '0150'
    union select '0100', '0160'
    union select '0100', '0170'
    union select '0100', '0180'
    union select '0100', '0190'
    union select '0100', '0200'
    union select '0100', '0240'
    union select '0100', '0340'
    union select '0100', '0380'
    union select '0100', '0385'
    union select '0100', '0390'
    union select '0100', '0425'
    union select '0100', '0430'
    union select '0100', '0440'
    union select '0110', '0120'
    union select '0110', '0130'
    union select '0110', '0133'
    union select '0110', '0136'
    union select '0110', '0138'
    union select '0120', '0130'
    union select '0120', '0133'
    union select '0120', '0136'
    union select '0120', '0138'
    union select '0130', NULL
    union select '0133', NULL
    union select '0136', NULL
    union select '0138', NULL
    union select '0140', '0150'
    union select '0140', '0160'
    union select '0140', '0170'
    union select '0140', '0180'
    union select '0140', '0190'
    union select '0140', '0200'
    union select '0140', '0210'
    union select '0140', '0220'
    union select '0140', '0230'
    union select '0140', '0240'
    union select '0140', '0250'
    union select '0140', '0280'
    union select '0140', '0310'
    union select '0140', '0340'
    union select '0140', '0350'
    union select '0140', '0360'
    union select '0140', '0370'
    union select '0140', '0380'
    union select '0140', '0385'
    union select '0140', '0390'
    union select '0140', '0400'
    union select '0140', '0410'
    union select '0140', '0420'
    union select '0140', '0425'
    union select '0150', NULL
    union select '0160', NULL
    union select '0170', NULL
    union select '0190', NULL
    union select '0194', NULL
    union select '0198', NULL
    union select '0200', '0210'
    union select '0200', '0220'
    union select '0200', '0221'
    union select '0200', '0222'
    union select '0200', '0230'
    union select '0200', '0231'
    union select '0200', '0232'
    union select '0210', '0211'
    union select '0210', '0212'
    union select '0211', NULL
    union select '0212', NULL
    union select '0220', '0221'
    union select '0220', '0222'
    union select '0221', NULL
    union select '0230', '0231'
    union select '0230', '0232'
    union select '0231', NULL
    union select '0232', NULL
    union select '0240', '0250'
    union select '0240', '0260'
    union select '0240', '0270'
    union select '0240', '0280'
    union select '0240', '0290'
    union select '0240', '0300'
    union select '0240', '0310'
    union select '0240', '0320'
    union select '0240', '0330'
    union select '0250', '0260'
    union select '0250', '0270'
    union select '0280', '0290'
    union select '0280', '0300'
    union select '0310', '0320'
    union select '0310', '0330'
    union select '0340', '0350'
    union select '0340', '0360'
    union select '0340', '0370'
    union select '0390', '0400'
    union select '0390', '0410'
    union select '0390', '0420'

    select * from #Table1

    and this is the result

    create table #Result
    (
    _Order int
    , _Field varchar(4)
    )
    go
    insert into #Result
    select 1,'0040'
    union select 1,'0050'
    union select 1,'0080'
    union select 1,'0090'
    union select 1,'0091'
    union select 1,'0092'
    union select 1,'0130'
    union select 1,'0133'
    union select 1,'0136'
    union select 1,'0138'
    union select 1,'0150'
    union select 1,'0160'
    union select 1,'0170'
    union select 1,'0180'
    union select 1,'0190'
    union select 1,'0194'
    union select 1,'0198'
    union select 1,'0211'
    union select 1,'0212'
    union select 1,'0221'
    union select 1,'0222'
    union select 1,'0231'
    union select 1,'0232'
    union select 1,'0260'
    union select 1,'0270'
    union select 1,'0290'
    union select 1,'0300'
    union select 1,'0320'
    union select 1,'0330'
    union select 1,'0350'
    union select 1,'0360'
    union select 1,'0370'
    union select 1,'0380'
    union select 1,'0385'
    union select 1,'0400'
    union select 1,'0410'
    union select 1,'0420'
    union select 1,'0425'
    union select 1,'0430'
    union select 1,'0440'
    union select 2,'0030'
    union select 2,'0060'
    union select 2,'0070'
    union select 2,'0120'
    union select 2,'0210'
    union select 2,'0220'
    union select 2,'0230'
    union select 2,'0250'
    union select 2,'0280'
    union select 2,'0310'
    union select 2,'0340'
    union select 2,'0390'
    union select 3,'0020'
    union select 3,'0110'
    union select 3,'0200'
    union select 3,'0240'
    union select 4,'0140'
    union select 5,'0100'
    union select 6,'0010'

    level 1 have no dependencies and level 6 has most dependencies.  obviously i dont know the levels thats what needs to be calculated.

    Thanks again

  • Thanks ill take a look

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply