Display records as another field name in query

[JtheRipper]

Hi there,

I have a table that looks something like this: (parent-child relationship table)

Device_id   parent  type  device_name ip        mac 
1           1       ROOT  ROOT       1.1.1.1    ABC        
2           1       NW    NETP       1.1.2.3    ABS        
3           2       SLOT  1 
4           3       PORT  1
5           3       PORT  2
6           3       PORT  3
7           5       LINE  1
8           6       LINE  2
9           8       PC    PC1        1.2.3.4    CBD

If I can explain any further for example all the children of the parent device (id=2):

              ->PORT1
NETP -> SLOT1 ->PORT2 ->LINE1
              ->PORT3 ->LINE2 ->PC1

And so on...

Now, I want to know how do I can display the values like this for a given device_id:

Port    line    ip         mac        PC
1             
2       1                 
3       2       1.2.3.4    CBD        PC1

Can this be done using a select statement using joins or do I have to write a cursor-type query to perform this?

Any input very much appreciated!

Thanks,
J.

 

[leveyp]

This worked OK when I was trying to recurse though a bill of materials

WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS
(
SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART = '01'
UNION ALL
SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART
AND PARENT.LEVEL < 2
)
SELECT PART, LEVEL, SUBPART, QUANTITY
FROM RPL;

 

[JtheRipper]

Hi,

Thanks for the example. I kind of answered my own question using cursors, only problem is that you must know how many levels deep your data goes.

Here is my code for anyone interested:

SET NOCOUNT ON

DECLARE @d_id int,
	@p_id int, 
	@d_name varchar(50),
   	@d_type varchar(50),
	@d_id2 int,
	@p_id2 int, 
	@d_name2 varchar(50),
   	@d_type2 varchar(50),
	@d_id3 int,
	@p_id3 int, 
	@d_name3 varchar(50),
   	@d_type3 varchar(50),
	@message varchar(500)

PRINT '-------- Device report --------'

SELECT @message = '----- HUB:  name-'+[name] + '  ID-'+cast( d_id as varchar)+'  PID-'+cast(p_id as varchar)
from dev_temp_tbl
PRINT @message

DECLARE device_cursor CURSOR FOR 
SELECT p_id, d_id, [name], type
FROM dev_temp_tbl
WHERE p_id=2
PRINT ' '

OPEN device_cursor
FETCH NEXT FROM device_cursor 
INTO @p_id, @d_id, @d_name, @d_type

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT ' '

   -- Declare an inner cursor based   
   -- on id from the outer cursor.

   DECLARE d2_cursor CURSOR FOR 
   SELECT p_id, d_id, [name], type
   FROM dev_temp_tbl 
   WHERE p_id = @d_id   -- Variable value from the outer cursor

   OPEN d2_cursor
   FETCH NEXT FROM d2_cursor INTO  @p_id2, @d_id2, @d_name2, @d_type2

   IF @@FETCH_STATUS <> 0 
      PRINT '         '     

   WHILE @@FETCH_STATUS = 0
   BEGIN
      
-- start of d3 cursor	
	declare d3_cursor cursor for
	SELECT p_id, d_id, [name], type
	FROM dev_temp_tbl 
	WHERE p_id = @d_id2   -- Variable value from the outer cursor

	   OPEN d3_cursor
	   FETCH NEXT FROM d3_cursor INTO  @p_id3, @d_id3, @d_name3, @d_type3
	   WHILE @@FETCH_STATUS = 0
	   BEGIN
		SELECT @message = @d_type+@d_name+' --> '+@d_type2+@d_name2+' --> '+@d_type3+' '+@d_name3
	        PRINT @message
	        FETCH NEXT FROM d3_cursor INTO  @p_id3, @d_id3, @d_name3, @d_type3
	   END
   CLOSE d3_cursor
   DEALLOCATE d3_cursor
-- end of d3 cursor

      FETCH NEXT FROM d2_cursor INTO @p_id2, @d_id2, @d_name2, @d_type2
   
   END

   CLOSE d2_cursor
   DEALLOCATE d2_cursor
   
   -- Get the next author.
   FETCH NEXT FROM device_cursor 
   INTO @p_id, @d_id, @d_name, @d_type
END

CLOSE device_cursor
DEALLOCATE device_cursor
GO

There is also a similar example in BOL if you do a search on "cursor".

Thanks,
J.