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
  • 34,719
  • 24
  • 185
  • 251
Jason Clark
  • 1,077
  • 6
  • 20
  • 48

1 Answers1

1

I think you can use table function that may help you

Ravi
  • 465
  • 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