INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need Help in Self Join

Need Help in Self Join

(OP)
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

RE: Need Help in Self Join

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.

RE: Need Help in Self Join

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close