LOOP AND UPDATE TABLE WITH FIRST 4 RECS

[LeonelSanchezJr]

MAINTABLE:

ID NAME
123 JOHN DOE


ID ADDRESS
123 1007 MAIN
123 2222 WEST 23RD
123 3333 SOUTH 2ND
123 4444 EAST 10TH

DESIRED OUTPUT:

ID NAME ADDR1 ADDR2 ADDR3 ADDR4
123 JOHN DOE 1007 MAIN 2222 WEST 3333 SOUTH 4444 EAS


THANKS

 

[Cagliostro]

This is a sollution, but is good for only one ID. As I belive, there should be sollutions for many IDs.

select top 1 t1.ID ID, t1.Addr Addr1, t2.Addr Addr2, t3.Addr3 Addr, t4.Addr Addr4, t5.Addr Addr5
from
(
select distinct
ID, ADDRESS Addr
from MAINTABLE
) t1 inner join
(
select distinct
ID, ADDRESS Addr
from MAINTABLE
) t2 on t1.mID = t2.mID and t2.Addr not in(t1.Addr)
inner join
(
select distinct
ID, ADDRESS Addr
from MAINTABLE
) t3 on
t1.mID = t3.mID and t3.Addr not in(t1.Addr, t2.Addr)
inner join
(
select distinct
ID, ADDRESS Addr
from MAINTABLE
) t4 on t1.mID = t4.mID and t4.Addr not in(t1.Addr, t2.Addr, t3.Addr)
inner join
(
select distinct
ID, ADDRESS Addr
from MAINTABLE
) t5 on t1.mID = t5.mID and t5.Addr5 not in(t1.Addr, t2.Addr, t3.Addr, t4.Addr)
where ID = YourChoise
order by mID, Addr1 , Addr2, Addr3, Addr4 John Fill