CODEWITHSUNDEEP
sqlconcatenationibm-midrangedb2-400
Mmbauer
Mmbauer

Reputation: 59

SQL concatenate rows into one field (DB2) With Filters

I am using DB2 V7r1 (so no listagg unfortunatly). I need to be able to have all the descriptions for the item(ItemNum) be in one string comma separated. I have a Query I found that kind of works but I can not filter the result by item number. It will work fine with the first 100 rows of data but if I try to filter out an item that is maybe 100,000 rows down the table, it takes forever.

*EDIT, I should add that this table contains 1,460,072 records, and one Item/Operation may have up to 60 Description entries, so if any one knows of a way to maybe pre-filter the results or a more efficient way to do this i would appreciate it greatly

Here is my table: PARTS

ItemNum      OpSequence      DscNum    Description
A-123         10               2       Desc Line 1
A-123         10               4       Desc Line 2
A-123         10               6       Desc Line 3
A-123         20               2       Desc Line 1
A-123         20               4       Desc Line 2
Z-555         10               2       Desc Line 1
Z-555         10               4       Desc Line 2

Here is the result I need (Need to filter by ItemNum and OpSequence)

ItemNum     OpSequence     Description
A-123        10            Desc Line 1, Desc Line 2, Desc Line 3

Here is the Query I used

 with x (ItemNum, OpSequence, cnt, list, empno, len) as
 (select z.ItemNum, z.OpSequence,
   (select count(*) from PARTS y 
    where y.ItemNum=z.ItemNum  
    group by y.ItemNum),
  cast(z.Description as varchar(100)),
  rrn(z), 1  
  from PARTS z 
  where z.ItemNum = 'A-123'  (HERE IS WHERE I AM TRYING TO FILTER)
 union all  
  select x.ItemNum,
     x.OpSequence,
     x.cnt,
     strip(x.list) ||', '|| e.Description,
     rrn(e),
     x.len+1   
  from PARTS e, x 
  where e.ItemNum = x.ItemNum and rrn(e) > x.empno   
 )  
 select ItemNum,OpSequence, list
 from x 
 where len=cnt

Upvotes: 1

Views: 5516

Answers (3)

Douglas Korinke
Douglas Korinke

Reputation: 399

I've used XMLAGG on iSeries V7R1 for something similar, try the below to get started. Note the below doesn't account for your need to get the minimum OpSequence but does get you a comma delimited list of the description field.

SELECT    ItemNum,
          OpSequence,
          DscNum,
          TRIM(REPLACE(  
                REPLACE(  
                  REPLACE(  
     XMLSERIALIZE(XMLAGG(XMLELEMENT(NAME "x", TRIM(Description)) ) AS VARCHAR(1000))  
     , '</x><x>', ',')  
     , '<x>', '')  
     , '</x>', '')) AS Description
FROM x
GROUP BY ItemNum, OpSequence, DscNum

Upvotes: 0

Paul Vernon
Paul Vernon

Reputation: 3901

If you have a limited number of Lines for any given item, this SQL would work for you.. you would need to extend it if you have more than 11 lines for example

SELECT ItemNum, OpSequence
,      MAX(CASE WHEN DscNum = 1 THEN         Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 2 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 3 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 4 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 5 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 6 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 7 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 8 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 9 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum =10 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum =11 THEN ', ' || Description ELSE '' END)
        AS Description
FROM
    PARTS
WHERE
    ItemNum = 'A-123'
GROUP BY
    ItemNum, OpSequence

Upvotes: 0

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

Although you do not have the listagg functionality XML functions will solve your problem. Before listagg was made available XMLAGG (and XMLGROUP) had been used and these are availible in DB2 V7r1.

Check out

Upvotes: 1

Related Questions