none
SQL query to get the details

    Question

  • We have a database with machine details such as ID, Hostname, IP Address, OS, etc...

    ID  IP Address      Hostname     Protocol    NetMask
    1   10.216.16.47    Test123         DNS     255.255.255.0
    1   10.216.16.48    Test123         DNS     255.255.255.0
    54  10.216.68.85    Test73711340    DNS     255.255.255.0
    71  10.216.63.101   Test737101230   DNS     255.255.255.0
    94  10.216.34.153   Test10000182    DNS     255.255.255.0

    I need to write a query which will find a machine with two IP addresses and give the output with a separate column for each IP.

    Expected output:

    ID  IP Address     IP Address 2     Hostname      Protocol      NetMask
    1   10.216.16.47   10.216.16.48    Test123         DNS      255.255.255.0
    54  10.216.68.85    Null           Test73711340    DNS      255.255.255.0
    71  10.216.63.101   Null           Test737101230   DNS      255.255.255.0
    94  10.216.34.153   Null           Test10000182    DNS      255.255.255.0

    Any Suggestions?

    Monday, August 11, 2014 2:39 PM

All replies

  • And exactly HOW do you propose to match rows?  Is it based on ID, HostName, a combination of columns, something else?  Will there be a situation where more than 2 rows are related?  What should happen when related rows do not have the same values for Protocol or NetMask or HostName? 
    Monday, August 11, 2014 2:55 PM
  • with cte as (
    
    select HostName, [Ip Address], row_number() over (partition by HostName order by[Ip Address]) as RowNumber from tbl
    
    )
    select HostName
    , [1] As ipaddress1
    , [2] As ipaddress2
    , [3] As ipaddress3
    , [4] As ipaddress4
    from cte 
    pivot (max([IP Address] for RowNumber in 
    ([1], [2], [3], [4]) ) as pvt


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by BKomm Monday, August 11, 2014 3:25 PM
    Monday, August 11, 2014 2:57 PM
  • I hope this helps - 

    DECLARE @tblName TABLE (ID INT  , IPAddress   VARCHAR(100),   Hostname     VARCHAR(100),Protocol    VARCHAR(100),NetMask VARCHAR(100) )


    INSERT INTO @tblName
    SELECT 1   ,'10.216.16.47    ','Test123         ','DNS     ','255.255.255.0' UNION ALL
    SELECT 1   ,'10.216.16.48    ','Test123         ','DNS     ','255.255.255.0' UNION ALL
    SELECT 54  ,'10.216.68.85    ','Test73711340    ','DNS     ','255.255.255.0' UNION ALL
    SELECT 71  ,'10.216.63.101   ','Test737101230   ','DNS     ','255.255.255.0' UNION ALL
    SELECT 94  ,'10.216.34.153   ','Test10000182    ','DNS     ','255.255.255.0' 

    ;WITH CTE AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY IPAddress) AS  Row_NUM from @tblName)

    select ID,[1] as IPAddress, [2] as IPAddress2,Hostname , Protocol, NetMask  FROM CTE PIVOT (max(IPAddress) FOR Row_NUM in ([1],[2])) PVT


    MCTS 2008 & 2005 , MCITP 2008 -- Please remember to mark the post as answered if it answers your question.

    Monday, August 11, 2014 3:59 PM
  • SELECT ID,
    MAX(CASE WHEN Seq = 1 THEN IPAddress END) AS IPAddress1,
    MAX(CASE WHEN Seq = 2 THEN IPAddress END) AS IPAddress2,
    Hostname,
    Protocol,
    NetMask
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY ID,Hostname,Protocol,NetMask ORDER BY IPAddress) AS Seq,*
    FROM table
    )t
    GROUP BY ID,Hostname,Protocol,NetMask


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, August 11, 2014 4:30 PM
  • It is based on ID's ..Id is the unique value for the machines.

    There might be situation where more than two rows might be related, we need to get IP Address2, 3 ,4 and so on if so..

    If there are different protocols and netmask, we can ignore those as we only need the IP address and hostname with ID.

    Tuesday, August 12, 2014 12:25 PM
  • This works when we have 2 IP Address, what if  there are more than 2?
    Wednesday, August 13, 2014 9:29 AM