quote:

---

It's a poor design

---

quote:

---

someone could've easily had more fields and called them Comment2, Comment3, Comment4....and so on.

---

quote:

---

Originally posted by NadJ
It's a poor design, someone could've easily had more fields and called them Comment2, Comment3, Comment4....and so on.

---

quote:

---

The data I have given is made up. It makes no sense, I know

---

quote:

---

There must be a simpler method to do what I am after, I hope this is not a case of seasoned experts over flexing their muscles

---

select top 50 ID, NAME, FAA_ID from Airports where ID like 'US%' ORDER BY ID 
select top 50 ID, R1, R2 from Runways where ID like 'US%' ORDER BY ID

quote:

---

Originally posted by nr

OK and what are you trying to do?
How do you know that the two entries for US00071 are the same runway and is that important?

It looks like the second table should have a column to identify unique runways per airport but maybe it's not needed from a system point of view.

What are you trying to do with the data?

---

quote:

---

As for your second question - trust me I know these things being in the field! (no seriously there are a hundred and one sources that can verify it)

---

--create a temp table
Select
    ID, R1, R2
into
    #workingtable
from
    Runways
where ID like 'US%'
group by ID, R1, R2

--create a table to hold the lists
create table #Temp1 (
    i int not null primary key,
    list varchar(8000) not null)

declare
    @i int,
    @maxrowid int,
    @sql varchar(8000),
    @list varchar(8000)

select
    @maxrowid = count(ID), --using count because ID is not an integer
    @i = 0
from
    #workingtable

while @i <= @maxrowid
begin

    select
        @sql = 'select ID' --********FAILS HERE*********--
    from
        #workingtable
    where
        rowid = @i

    exec sp_MakeCharList
        @codelistselect=@sql,
        @delimitedlist=@list output

    insert into #temp1 (i,list)
    select i, @list
    from
        #workingtable
    where
        rowid = @i and
        @list is not null
    select @i = @i + 1
end

--return a sample from the final rowset
select top 10
    i,
    case
        when len(list) > 50 then convert(varchar(50), left(list,47) + '...')
        else convert(varchar(50),list)
    end as list
from
    #temp1
order by
    newid()
go