Desing of a table and Hirerarchy - IP's
-
want to build a table, which contains different ip and there sub-net.
when i pull the table i want to get result in way i know who os the root, who is the son's, and son on
for example :
10.0.0.0
10.13.15.63
10.16.8.2
15.0.0.0
15.3.6.8
15.11.2.8
and son on.....
-
wanttolearn1 (12/13/2016)
want to build a table, which contains different ip and there sub-net.when i pull the table i want to get result in way i know who os the root, who is the son's, and son on
for example :
10.0.0.0
10.13.15.63
10.16.8.2
15.0.0.0
15.3.6.8
15.11.2.8
and son on.....
OK, but what is your question? Welcome to the forum, by the way.
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.
-
Thanks
i want the result to be in some hierarchy (this is after each ip "," followed by depth level
10.0.0.0,0
10.13.15.63,1
10.16.8.2,1
15.0.0.0,0
15.3.6.8,1
15.3.7.8,1
15.3.8.0,1
15.3.8.1,2
-
wanttolearn1 (12/13/2016)
Thanksi want the result to be in some hierarchy (this is after each ip "," followed by depth level
10.0.0.0,0
10.13.15.63,1
10.16.8.2,1
15.0.0.0,0
15.3.6.8,1
15.3.7.8,1
15.3.8.0,1
15.3.8.1,2
Sorry, but that is not detailed enough for me to be able to help you. I don't know whether you want a table design, a view, a function, a stored procedure or some combination of these.
Please follow the link in my signature to understand how to post your question in such a way that it is easy for others to understand and work with.
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.
-
Please define "depth level".
Sounds like this is a great candidate for SSRS.
-
Welcome to SSC!
I'm still not 100% clear on what the output should look like and this solution might not be complete but it's a start...
DECLARE @sampledata TABLE(ip varchar(12) NOT NULL);
INSERT @sampledata
VALUES ('10.0.0.0'),('10.13.15.63'),('10.16.8.2'),('15.0.0.0'),
('15.3.6.8'),('15.11.2.8'),('15.3.8.0'),('15.3.0.0');
WITH getLevels AS
(
SELECT
ip,
lvl =
CASE
WHEN ip LIKE '%.0.0.0' THEN 0
WHEN ip LIKE '%[^0].0.0' THEN 1
WHEN ip LIKE '%[^0].0' THEN 2
ELSE 3
END,
network =
CASE
WHEN ip LIKE '%.0.0.0' THEN SUBSTRING(ip, 1, charindex('.',ip)-1)
WHEN ip LIKE '%[^0].0.0' THEN SUBSTRING(ip,1,charindex('.',ip, charindex('.',ip)+1)-1)
WHEN ip LIKE '%[^0].0' THEN SUBSTRING(ip,1,charindex('.',ip,charindex('.',ip, charindex('.',ip)+1)+1)-1)
ELSE ip
END
FROM @sampledata
)
SELECT
parent = CONCAT(gl1.ip, ',', gl1.lvl),
child = CONCAT(gl2.ip, ',', gl2.lvl)
FROM getLevels gl1
CROSS JOIN getLevels gl2
WHERE gl1.lvl < gl2.lvl
AND LEFT(gl2.network,LEN(gl1.network)) = gl1.network
ORDER BY gl1.ip, gl1.lvl -- not required and will slow you down... Included for demo purposes
Results:
parent child
------------------------- -------------------------
10.0.0.0,0 10.13.15.63,3
10.0.0.0,0 10.16.8.2,3
15.0.0.0,0 15.3.6.8,3
15.0.0.0,0 15.11.2.8,3
15.0.0.0,0 15.3.8.0,2
15.0.0.0,0 15.3.0.0,1
15.3.0.0,1 15.3.6.8,3
15.3.0.0,1 15.3.8.0,2
The WHERE clause still likely needs some tweaking to include "level 2" parents.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
wanttolearn1 (12/13/2016)
Thanksi want the result to be in some hierarchy (this is after each ip "," followed by depth level
10.0.0.0,010.13.15.63,1
10.16.8.2,1
15.0.0.0,0
15.3.6.8,1
15.3.7.8,1
15.3.8.0,1
15.3.8.1,2
No one could see the indentation unless they quoted your post above. I changed it from a QUOTE IFCode to a Code="plain" entry to see it.
Shifting gears, a bit of pattern recognition to drive some simple math makes short work of this.
--=====Simulate the test table. This is NOT a part of the solution.
DECLARE @TestTable TABLE(IP varchar(12) NOT NULL);
INSERT @TestTable
VALUES ('10.0.0.0')
,('10.13.15.63')
,('10.16.8.2')
,('15.0.0.0')
,('15.3.6.8')
,('15.3.7.8')
,('15.3.8.0')
,('15.3.8.1')
;
--===== Use some basic math to simplfy the problem.
WITH
cteSplit AS
( --=== Split the IP elements and convert to integers
SELECT IP
,IP4 = CONVERT(TINYINT,PARSENAME(ip,4))
,IP3 = CONVERT(TINYINT,PARSENAME(ip,3))
,IP2 = CONVERT(TINYINT,PARSENAME(ip,2))
,IP1 = CONVERT(TINYINT,PARSENAME(ip,1))
FROM @TestTable
)
,cteFindDepth AS
( --=== Determine the depth using some simple math
SELECT ip,IP4,IP3,IP2,IP1
,Depth = ROW_NUMBER() OVER (PARTITION BY IP4,IP3,IP2 ORDER BY IP4,IP3,IP2)*SIGN(IP3+IP2+IP1)
FROM cteSplit
)
--===== Indent the results according to depth.
-- Concatenate Depth if you want. I can't see denormalizing the column further.
SELECT IP = SPACE(Depth*4)+IP
,Depth
FROM cteFindDepth
ORDER BY IP4,IP3,IP2,IP1
;
Results:
IP Depth
=================== =====
10.0.0.0 0
10.13.15.63 1
10.16.8.2 1
15.0.0.0 0
15.3.6.8 1
15.3.7.8 1
15.3.8.0 1
15.3.8.1 2
(8 row(s) affected)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
to be more clear this is an example of what i want to produce (the number in the square is the subnet selected)
-
wanttolearn1 (12/13/2016)
to be more clear this is an example of what i want to produce (the number in the square is the subnet selected)
What software are you going to use to present your results?
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.
-
wanttolearn1 (12/13/2016)
to be more clear this is an example of what i want to produce (the number in the square is the subnet selected)So take what I did and make it work with the software you're using. It's just another column returned in the result set.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden there is some "bug"
15.11.0.0
15.3.0.0
should be under 15.0.0.0
how do i fix it?
-
wanttolearn1 (12/14/2016)
Jeff Moden there is some "bug"
15.11.0.0
15.3.0.0
should be under 15.0.0.0
how do i fix it?
The "bug" would be in whatever you did to the code I wrote or your presentation code because the original code I wrote works just fine.
--=====Simulate the test table. This is NOT a part of the solution.
DECLARE @TestTable TABLE(IP varchar(12) NOT NULL);
INSERT @TestTable
VALUES ('15.11.0.0') --Added this...
,('10.0.0.0')
,('10.13.15.63')
,('10.16.8.2')
,('15.0.0.0')
,('15.3.6.8')
,('15.3.7.8')
,('15.3.8.0')
,('15.3.8.1')
;
--===== Use some basic math to simplfy the problem.
WITH
cteSplit AS
( --=== Split the IP elements and convert to integers
SELECT IP
,IP4 = CONVERT(TINYINT,PARSENAME(ip,4))
,IP3 = CONVERT(TINYINT,PARSENAME(ip,3))
,IP2 = CONVERT(TINYINT,PARSENAME(ip,2))
,IP1 = CONVERT(TINYINT,PARSENAME(ip,1))
FROM @TestTable
)
,cteFindDepth AS
( --=== Determine the depth using some simple math
SELECT ip,IP4,IP3,IP2,IP1
,Depth = ROW_NUMBER() OVER (PARTITION BY IP4,IP3,IP2 ORDER BY IP4,IP3,IP2)*SIGN(IP3+IP2+IP1)
FROM cteSplit
)
--===== Indent the results according to depth.
-- Concatenate Depth if you want. I can't see denormalizing the column further.
SELECT IP = SPACE(Depth*4)+IP
,Depth
FROM cteFindDepth
ORDER BY IP4,IP3,IP2,IP1
;
Results:
IP Depth
=================== =====
10.0.0.0 0
10.13.15.63 1
10.16.8.2 1
15.0.0.0 0
15.3.6.8 1
15.3.7.8 1
15.3.8.0 1
15.3.8.1 2
15.11.0.0 1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/14/2016)
wanttolearn1 (12/13/2016)
to be more clear this is an example of what i want to produce (the number in the square is the subnet selected)So take what I did and make it work with the software you're using. It's just another column returned in the result set.
Hmm... In order to produce a hierarchy like the one from the screenshot the subnet mask needs to be added to the calculation, I think.
-
yes i agree! how can i do it?
-
to be more clear, its a little bit more complicated as the print screen show :
i can put under 10.20.12.0 (subnet 24) aagain same ip 10.20.12.0 (but with subnet 30 which mean smaller range of ip's)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply