Adding mulitple lines to one field

Transact-SQL
1

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.

2

Try this.
select OEC.ord_no,row_number() over (partition by OEC.ord_no order by OEC.cmt_seq_no) as id,OEC.comment
into #tmpout
from OER
join OEC on OEC.order_no = OER.order_no

declare @max int = 0,@id int = 1,@sql varchar(max) = '',@row varchar(max) = ''

select @max = (select max(id) from #tmpout)

while @id < @max
begin
select @row = @row + '[' + cast(@id as varchar(4)) + '],'
select @id = @id + 1
end

select @row = left(@row,len(@row) - 1)

select @sql =
'select *
from
(
select ord_no,id,comment
from #tmpout
) a
pivot (max(comment) for id in (' + @row + ')) as b'

exec (@sql)

3

Hi

Please find ur solution

SELECT p1.partNo
	,stuff((
			SELECT ',' + Comments
			FROM (
				SELECT a.partNo
					,b.Comments
				FROM #SampleData_Parts a
				INNER JOIN #SampleData_Comments b ON a.partNo = b.partNo
				) p2
			WHERE p2.partNo = p1.partNo
			ORDER BY partNo
			FOR XML PATH('')
			), 1, 1, '') AS Comments
FROM (
	SELECT a.partNo
		,b.Comments
	FROM #SampleData_Parts a
	INNER JOIN #SampleData_Comments b ON a.partNo = b.partNo
	) p1
GROUP BY p1.partNo

/*
drop table #SampleData_Parts
create table #SampleData_Parts
(
partNo int null,
orderNo int null
)
insert into #SampleData_Parts select 1,2
insert into #SampleData_Parts select 2,3
insert into #SampleData_Parts select 3,5

drop table #SampleData_Comments
create table #SampleData_Comments
(
partNo int null,
comments varchar(100) null
)
insert into #SampleData_Comments select 1,'ha ha ha'
insert into #SampleData_Comments select 1,'Ok'
insert into #SampleData_Comments select 1,'Fine'
insert into #SampleData_Comments select 2,'oops'
insert into #SampleData_Comments select 2,'sad'
insert into #SampleData_Comments select 2,'yes yes'
insert into #SampleData_Comments select 2,'no no'
insert into #SampleData_Comments select 3,'aaaaaa'
insert into #SampleData_Comments select 3,'xxxxxxxxxxx'

*/

4

Probably needs to also ORDER BY on some sort of Sequence / ItemNo column (in SampleData_Comments) too? (O/P's sample looked like there is a [seq_no] column available)

Otherwise they will be concatenated in random order (well ... most of the time the order will be "as execpted" but occasionally it will be random, so ordering by Sequence/ItemNo or, at the very least, some unique key(s) in SampleData_Comments will produce a repeatable order!)

5

If you look at my solution Kirsten, it creates an sort id based upon the comment and sort sequence (using row_number()) to get around the issue you're describing.

6

I haven't looked at your code closely, so correct me if I am wrong, but your Row_Number() OVER() has an ORDER BY OEC.cmt_seq_no and a PARTITION BY OEC.ord_no and references the two tables OER and OEC. If those two ORDER BY columns uniquely identify rows selected from those two tables that's fine, but if not then the sequence of Row_Number is not guaranteed to be repeatable each time and will, essentially, be random. Row_Number will be available to uniquely describe the rows in that resultset, but if you run it again you may get a different sequencing and thus the records that the Row_Number refers to may be different.