Need Help in Self Join

[raccess]

Hi,

I need help in developing one SQL for self join View.

My table structure is as follows,

ID Type SeqNo TimeStmp Text

1 N 1 05/01/2003 text1 N
1 N 2 05/02/2003 text2 N
1 B 1 05/01/2003 text1 B
1 B 2 05/03/2003 text2 B
1 B 3 05/05/2003 text3 B


Now i want to develop one view to see data in following format,

ID TextN TextB
1 05/01/2003 - text1 N ## 05/02/2003 - text2 N 05/01/2003 - text1 B ## 05/03/2003 - Text2 B ## 05/05/2003 - text3 B


In this format i want to create single record for each ID and concat all data for same type. If type is N then concat timestamp & Text information for all SeqNo with same ID.

Could anyone please tell me which type of SQL i have to write to get data in this format?

Thanks in Advance,

Raccess

 

[Dagon]

I don't think you can do this with self-joins unless you know how many records for each ID there are. If there are at most 3, then you could do it by outer joining the table the table to itself e.g

select a1.id, a1.text || a2.text || a3.text
from
(select * from table where id = 1) a1
left join
(select * from table where id = 2) a2
on a1.id = a2.id
left join
(select * from table where id = 3) a2
on a1.id = a3.id

This assumes the "id=1" row is always populated. However, if you don't have an upper limit on the number of rows for an id or it is very high, then this method is impossible.

The only alternative I can think of would be to write a stored procedure which opens a cursor and loops through the table in order of id, seq_no. It then builds up the two strings you want and writes them out to another table.

 

[raccess]

Thanks for your quick reply. Yes i don't have any upper limit on number of rows for ID.

I think i have to go for Stored procedure only.

Thank You