help with a query?

Question

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

Answer 1

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

Answer 2

Thank you Naomi.but i can have 'n' number of IpAddresses for each ID.

Can i try the query for any number of IPAddresses.thanks you for your Quick response :)

---

lucky

Answer 3

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

http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 4

thank you so much for the article.i am checking it.

---

lucky

Answer 5

your article worked perfectly for me.thanks so much

---

lucky