asked on

Combine data from many rows into single row

I have a master table called tbl_Job in which I want to populate a 4000char string, by pulling info from the child table tbl_JobBOM. There can be up to 99 records in tbl_JobBOM from which several columns need extracted and placed into the parent table in a single row, single column, per PK.

WITH CTE AS 
(
SELECT 
    ItemNo + ' ' +QtyRequired) + ' ' + QtyOpen +  CHAR(13) + CHAR(10) 
  AS CompDetails, 
  
  ROW_NUMBER() OVER (PARTITION BY JobOper ORDER BY ItemNo) AS Seq,
    JobOper FROM tbl_JobBOM 
)  
    
UPDATE b SET ComponentsThisOper = 
LEFT( 
+ (SELECT MAX(CompDetails) FROM CTE WHERE JobOper=b.JobOper AND Seq=1) 
+ (SELECT MAX(CompDetails) FROM CTE WHERE JobOper=b.JobOper AND Seq=2) 
+ (SELECT MAX(CompDetails) FROM CTE WHERE JobOper=b.JobOper AND Seq=3) 
+ (SELECT MAX(CompDetails) FROM CTE WHERE JobOper=b.JobOper AND Seq=4) 
--... 
+ (SELECT MAX(CompDetails) FROM CTE WHERE JobOper=b.JobOper AND Seq=97) 
+ (SELECT MAX(CompDetails) FROM CTE WHERE JobOper=b.JobOper AND Seq=98) 
+ (SELECT MAX(CompDetails) FROM CTE WHERE JobOper=b.JobOper AND Seq=99)
,4000) 

FROM tbl_Job b

Open in new window

The tables are large and the query takes 2 minutes to run. I'm looking for a better way to do this, that runs much faster and doesn't require the 99 lines of select statements.

Can someone please send a working code snippet to illustrate a better approach?

I have searched all over Google and have read about pivots, coalesce, etc but am not able to understand how to apply the concepts to this problem.

Thanks in advance

momi_sabag

try this
WITH CTE AS
(
SELECT
ItemNo + ' ' +QtyRequired) + ' ' + QtyOpen + CHAR(13) + CHAR(10)
AS CompDetails,

ROW_NUMBER() OVER (PARTITION BY JobOper ORDER BY ItemNo) AS Seq,
JobOper FROM tbl_JobBOM
) ,
cte2 (CompDetails) as (
select CompDetails from cte where seq between 1 and 99 order by seq for xml path('')
)
UPDATE b SET ComponentsThisOper =
LEFT( CompDetails,4000)

FROM tbl_Job b

LovinSpoonful

ASKER

I am not able to make this work.

Here is what I can make work:

WITH a AS (
SELECT JobOper,
ItemNo + ' ' + CONVERT(VARCHAR,QtyRequired) + ' ' + CONVERT(VARCHAR,QtyOpen) + CHAR(13) + CHAR(10) AS CompDetails,
ROW_NUMBER() OVER (PARTITION BY JobOper ORDER BY ItemNo) AS Seq
FROM tbl_JobBOM
)

SELECT CompDetails FROM a WHERE a.Seq between 1 and 99
and JobOper = '623417.150'
ORDER BY seq FOR XML PATH('')

result:

<CompDetails>abcdef 6 0
</CompDetails>
<CompDetails>ghijklm 3 0
</CompDetails>
<CompDetails>nopqrs 3 0
</CompDetails>

I need to get the JobOper attached to each XML record, then update with a join.
I tried this but it doesn't work:

WITH a AS (
SELECT JobOper,
ItemNo + ' ' + CONVERT(VARCHAR,QtyRequired) + ' ' + CONVERT(VARCHAR,QtyOpen) + CHAR(13) + CHAR(10) AS CompDetails,
ROW_NUMBER() OVER (PARTITION BY JobOper ORDER BY ItemNo) AS Seq
FROM tbl_JobBOM
)

, b (JobOper, CompDetails) AS (SELECT JobOper, CompDetails FROM a WHERE a.Seq between 1 and 99 ORDER BY seq FOR XML PATH(''))

UPDATE j SET ComponentsThisOper = LEFT(b.CompDetails,4000)
FROM tbl_Job j
INNER JOIN b ON j.JobOper = b.JobOper
WHERE j.JobOper = b.JobOper

the error is:
Msg 8159, Level 16, State 1, Line 9
'b' has fewer columns than were specified in the column list.

ASKER CERTIFIED SOLUTION

LovinSpoonful

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LovinSpoonful

ASKER

didn't get an answer from the community