I need to be able to merge multiple records into one master record.
I am joining multiple tables on a field "orderno". Because some tables have more than 1 record per "orderno", I get multiple records returned. The problem is that 1 record will contain some of the data I need and another record will contain the rest of the data I need. I need to combine the records and show all of the data within one main record.
I need to be able to have a value for the "coverweight" and a value for the "textweight" held within one record
I have attached a sample of my code and also a sample of the records returned for a specific "orderno". Basically, I need to combine the rows so that all records that are the same stay the same, but any records that are different are added in
CASE WHEN iscover = 1 THEN papergsm else '0' END AS [cover weight],
CASE WHEN iscover = 0 THEN papergsm else '0' END AS [text weight]
inner join orderheader on section.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer = customers.dbo.company.coid
inner join jobheader on orderheader.orderno = jobheader.orderno
inner join binding on jobheader.orderno = binding.orderno
inner join sourcingrequest on jobheader.jobno = sourcingrequest.jobno
left outer join sourcingrequestxref on sourcingrequest.sourcingid = sourcingrequestxref.sourcingid
left outer join sourcingpapermaster on sourcingrequestxref.paperid = sourcingpapermaster.paperid
left outer join sourcingbrands on sourcingpapermaster.pbid = sourcingbrands.brandid
where section.recordtype = 'ppe' and binding.recordtype = 'ppe' and orderheader.completedate > '2008-01-01' and binding.description not like '%guillotine%' and binding.description not like '%folder%' and binding.description not like '%sitma%'and binding.description not like '%pre stitch%' and jobheader.jobno = '7005'
order by orderheader.completedate