Need help grouping multiple columns into one

Question

I've listed my query below and the results that I'm currently getting. I would like to combine the multiple returns in the "CLASS" column to only return one value if possible.

results

SH DATE    NUMBER     CLASS
2014-7-1   123456     125
2014-7-1   123456     150

I would like to see it as

SH DATE    NUMBER     CLASS
2014-7-1   123456     125, 150

QUERY

SELECT 
DATE(B.TSTAMP) AS "SH DATE"
,TRIM(B.NUMBER) AS "NUMBER"
,CASE WHEN B.REPORT_TS IS NULL THEN '' ELSE CHAR(DATE(B.REPORT_TS)) END AS "DATE"
,F.CLASS AS "CLASS"
,S.CITY AS "ORIGIN CITY"
,S.STATE AS "ORIGIN STATE"
,S.ZIPCODE AS "ORIGIN ZIP"
,C.CITY AS "DESTINATION CITY"
,C.STATE AS "DESTINATION STATE"
,C.ZIPCODE AS "DESTINATION ZIP"
,B.WEIGHT

FROM AF.BLUE B

INNER JOIN AF.BILL F
ON F.NUMBER = B.NUMBER
AND F.CORRECTION = B.CORRECTION
AND F.CLASS <> ''

INNER JOIN AF.NAME S
ON S.NUMBER = B.NUMBER
AND S.CORRECTION = B.CORRECTION
AND S.TYPE = 'S'

INNER JOIN AF.NAME C
ON C.NUMBER = B.NUMBER
AND C.CORRECTION = B.CORRECTION
AND C.TYPE = 'C'

WHERE B.CUST = '11111'
AND (MONTH(CURRENT DATE)-1) = MONTH(B.TSTAMP)
AND B.CORR = ''

ListAgg is the function your looking for

xQbert
– xQbert

2014-08-20 22:00:16 +00:00
Commented Aug 20, 2014 at 22:00 — xQbert
– xQbert, Commented Aug 20, 2014 at 22:00
What version of DB2 ... maybe listAgg isn't supported.

xQbert
– xQbert

2014-08-20 22:38:13 +00:00
Commented Aug 20, 2014 at 22:38 — xQbert
– xQbert, Commented Aug 20, 2014 at 22:38

xQbert · Accepted Answer · 2014-08-20 22:28:29Z

1

ListAgg Docs

SELECT 
DATE(B.TSTAMP) AS "SH DATE"
,TRIM(B.NUMBER) AS "NUMBER"
,CASE WHEN B.REPORT_TS IS NULL THEN '' ELSE CHAR(DATE(B.REPORT_TS)) END AS "DATE"
,ListAgg(F.CLASS, ', ') WITHIN GROUP (Order by F.Class) AS "CLASS"
,S.CITY AS "ORIGIN CITY"
,S.STATE AS "ORIGIN STATE"
,S.ZIPCODE AS "ORIGIN ZIP"
,C.CITY AS "DESTINATION CITY"
,C.STATE AS "DESTINATION STATE"
,C.ZIPCODE AS "DESTINATION ZIP"
,B.WEIGHT

FROM AF.BLUE B

INNER JOIN AF.BILL F
ON F.NUMBER = B.NUMBER
AND F.CORRECTION = B.CORRECTION
AND F.CLASS <> ''

INNER JOIN AF.NAME S
ON S.NUMBER = B.NUMBER
AND S.CORRECTION = B.CORRECTION
AND S.TYPE = 'S'

INNER JOIN AF.NAME C
ON C.NUMBER = B.NUMBER
AND C.CORRECTION = B.CORRECTION
AND C.TYPE = 'C'

WHERE B.CUST = '11111'
AND (MONTH(CURRENT DATE)-1) = MONTH(B.TSTAMP)
AND B.CORR = ''

GROUP BY 
DATE(B.TSTAMP) 
,TRIM(B.NUMBER) 
,CASE WHEN B.REPORT_TS IS NULL THEN '' ELSE CHAR(DATE(B.REPORT_TS)) END 
,S.CITY 
,S.STATE 
,S.ZIPCODE
,C.CITY 
,C.STATE 
,C.ZIPCODE
,B.WEIGHT

edited Aug 20, 2014 at 22:28

answered Aug 20, 2014 at 22:02

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Robblob Over a year ago

Getting the following error when running your query ERROR [42601] [IBM][DB2] SQL0199N The use of the reserved word "GROUP" following "" is not valid. Expected tokens may include: ", FROM INTO".

xQbert Over a year ago

Sorry group by statements can't be aliased or I need to use alias names take your pick. I removed the "as ColName" from the group by statement.

Robblob Over a year ago

So I can't use the "S, B, C" prefix in the group by? Is that what you're saying? Those are definitely needed in my query to avoid any ambiguity.

xQbert Over a year ago

No it's the column aliases that I removed. Table aliases remain. And the aliases JUST needed to be removed in the group by. They are fine in the select. Additionally what version of DB2 is this... perhaps listagg isn't supported in your version.

Robblob Over a year ago

Still getting the same error. I even tried removing the CASE statement and DATE/TRIM functions. Hmmmm

|

Collectives™ on Stack Overflow

Need help grouping multiple columns into one

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related