Answered by:
help with a query?

-
i have two tables 1st table has id,name 2nd tables has ipaddress
for ex:
tableID -ID Primary Key tableIPAddress--Forign key tableID on Colum ID
------------- ----------------------
id name ID IpAddress
1 Abc 1 156.3.23.23
2 def 2 212.34.23.43
3 ijk 1 76.323.213.12
i am trying to retrive all the columns in TableID and TableIpAddress that have IPaddress
select ID,Name,I.IPAddress from TableID JOIN TableIPAddress I ON ID = I.ID.i get the below result
ID Name IPAddress
1 Abc 156.3.23.23
1 Abc 76.323.213.12
2 def 212.34.23.43
is there anyway to write the Query so the result would look in the below format?
ID Name IPAddress1 IPAddress2
1 Abc 156.3.23.23 76.323.213.12
2 def 212.34.23.43
lucky
- Edited by LuckyDba Tuesday, July 23, 2013 9:53 PM
Question
Answers
-
Do you have 2 rows maximum for each IP address? If so, then
;with cte as (select I.Id, I.Name, A.IpAddress,
ROW_NUMBER() over (partition by I.ID order by A.IPAddressId) as Rn from TableID I INNER JOIN tableIPAddress A on I.Id = A.ID) select Id, Name, [1] as IpAddress1, [2] as IpAddress2 from cte
PIVOT (max(IpAddress) FOR Rn IN ([1],[2])) pvt
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles- Marked as answer by LuckyDba Tuesday, July 23, 2013 10:06 PM
-
If the number of items is not known in advance but relatively small, we're talking about dynamic pivot. Lucky for you I have the solution in my TechNet article
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles- Marked as answer by LuckyDba Wednesday, July 24, 2013 4:39 PM
All replies
-
Do you have 2 rows maximum for each IP address? If so, then
;with cte as (select I.Id, I.Name, A.IpAddress,
ROW_NUMBER() over (partition by I.ID order by A.IPAddressId) as Rn from TableID I INNER JOIN tableIPAddress A on I.Id = A.ID) select Id, Name, [1] as IpAddress1, [2] as IpAddress2 from cte
PIVOT (max(IpAddress) FOR Rn IN ([1],[2])) pvt
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles- Marked as answer by LuckyDba Tuesday, July 23, 2013 10:06 PM
-
-
If the number of items is not known in advance but relatively small, we're talking about dynamic pivot. Lucky for you I have the solution in my TechNet article
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles- Marked as answer by LuckyDba Wednesday, July 24, 2013 4:39 PM
-
-