September 29, 2022 at 5:14 pm
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
September 29, 2022 at 7:10 pm
Please provide your sample data in the form of CREATE TABLE/INSERT statements. Also provide your desired results, based on the sample data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 29, 2022 at 7:46 pm
You have an adjacency list model. If there are not too many levels of hierarchy, querying can be handled via CTE. But you might want to consider nested sets:
September 29, 2022 at 8:53 pm
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
September 29, 2022 at 8:54 pm
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