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.
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
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?
with cte as ( select HostName, [Ip Address], row_number() over (partition by HostName order by[Ip Address]) as RowNumber from tbl ) select HostName ,  As ipaddress1 ,  As ipaddress2 ,  As ipaddress3 ,  As ipaddress4 from cte pivot (max([IP Address] for RowNumber in (, , , ) ) 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
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, as IPAddress,  as IPAddress2,Hostname , Protocol, NetMask FROM CTE PIVOT (max(IPAddress) FOR Row_NUM in (,)) PVT
MCTS 2008 & 2005 , MCITP 2008 -- Please remember to mark the post as answered if it answers your question.
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
- Proposed as answer by Jinchun ChenMicrosoft employee, Moderator Tuesday, August 12, 2014 3:16 AM
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.