SQL query to get the details


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

    ID  IP Address      Hostname     Protocol    NetMask
    1    Test123         DNS
    1    Test123         DNS
    54    Test73711340    DNS
    71   Test737101230   DNS
    94   Test10000182    DNS

    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    Test123         DNS
    54    Null           Test73711340    DNS
    71   Null           Test737101230   DNS
    94   Null           Test10000182    DNS

    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:

    • 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   ,'    ','Test123         ','DNS     ','' UNION ALL
    SELECT 1   ,'    ','Test123         ','DNS     ','' UNION ALL
    SELECT 54  ,'    ','Test73711340    ','DNS     ','' UNION ALL
    SELECT 71  ,'   ','Test737101230   ','DNS     ','' UNION ALL
    SELECT 94  ,'   ','Test10000182    ','DNS     ','' 


    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
    MAX(CASE WHEN Seq = 1 THEN IPAddress END) AS IPAddress1,
    MAX(CASE WHEN Seq = 2 THEN IPAddress END) AS IPAddress2,
    SELECT ROW_NUMBER() OVER (PARTITION BY ID,Hostname,Protocol,NetMask ORDER BY IPAddress) AS Seq,*
    FROM table
    GROUP BY ID,Hostname,Protocol,NetMask

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    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