0

I would like to create a view that has a part number, order number and any comments attached to the part number. Comments are stored in a table OEC. There is a record for each line of comments.

OEC.ord_no OEC.seq_no OEC.cmt_seq_no OEC.comment
123        1          1              Comment line 1
123        1          2              Comment line 2

The OEC will link to an OER table by OER.ord_no and OER.seq_no

I would like the view to return

OER.ord_no OER.item_no OEC_Comment
123        ABC         Comment line 1, Comment line 2

There is no limit to the number of lines a comment could possibly have

Cœur
  • 37,241
  • 25
  • 195
  • 267
Jason Clark
  • 1,307
  • 6
  • 26
  • 51

1 Answers1

1

I think you can use table function that may help you

Ravi
  • 475
  • 4
  • 12
  • how can i use table function? – Jason Clark Aug 24 '15 at 05:22
  • 1
    select OEC.ord_no,OER.item_no,stuff((select ','+OEC.OEC_Comment from OEC where OEC.seq_no = OER.seq_no for xml path('')),1,1,'') as OEC_Comment from OEC t join OER o on (o.ord_no = t.ord_no and o.seq_no = t.seq_no) group by OEC.ord_no,OER.item_no,OER.seq_no. try this –  Aug 24 '15 at 06:01