Comma Separated Detail from a sub table

This concept applies to any Header Detail table relationship, for this example we'll talk about the SOP header/detail/serial lot relationship. We want back one line for each item in the order; and we want the serial/lots concatenated into one field. The final result will look like this:

The entire query that we need is below. Run the second script first, it's the function that makes the whole thing work.

 This code is based on the TWO database

Related Articles

... and you 'll find more on the SQL (General) Menu

 Here's the query in its final form

select h.sopnumbe , h.custnmbr, l.itemnmbr, l.unitprce, l.quantity, l.xtndprce, dbo.ddf_CommaSeparatedLots(l.sopnumbe, l.soptype, l.lnitmseq) as lots
    from sop10100 h
        join sop10200 l on h.sopnumbe = l.sopnumbe and h.soptype = l.soptype
    where h.sopnumbe = 'FULORD1007'

 This function makes it work. It will concatonate all the lots for one item into one field

IF OBJECT_ID (N'dbo.ddf_CommaSeparatedLots') IS NOT NULL
   DROP FUNCTION dbo.ddf_CommaSeparatedLots
GO
   
CREATE FUNCTION dbo.ddf_CommaSeparatedLots (@sopnumbe varchar(21), @soptype smallint, @lnitmseq int)
   
RETURNS varchar(max)
   
AS
 
 
BEGIN
    declare @Lot as varchar(max)
    select @Lot = coalesce(@Lot + ',','') + rtrim(serltnum)
        from sop10201
        where sopnumbe = @sopnumbe
            and soptype = @soptype
            and lnitmseq = @lnitmseq
   
    return @lot 
END
 
GO

 

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences