SQL to combine many rows

Database

SQL to combine many rows

Sorry for such a basic question. I have a report I need to run with three fields, which will be part of a subquery. I want to end up with one row for each SPA_ID. For each SPA_ID there can be several RMRK_DATA_TYP_CD, which is a 6 character identifier. For each RMRK_DATA_TYP_CD there can be one to several SPA_RMRK_DESC, which is a memo field with comments.

I'd like to combine all the RMRK_DATA_TYP_CD and SPA_RMRK_DESC rows into one. Is that possible?

Here's the sample query:

SELECT

SPA_ID

, RMRK_DATA_TYP_CD

, SPA_RMRK_DESC

FROM WWVW101.SPA_PARTY_RMRK

Here’s how the data looks for one SPA_ID:

SPA_ID

RMRK_DATA_TYP_CD

SPA_RMRK_DESC

1,274,667

SPTERM

Test Comments

1,274,667

DLRCOM

More comments

1,274,667

DLRCOM

Even more comments

1,274,667

COMRMK

Test Comments again

And here’s where I’d like to end up. Spaces or other delimiters between each would be even better:

SPA_ID

COMMENTS

1,274,667

SPTERMTest CommentsDLRCOMMore commentsDLRCOMEven more commentsCOMRMKTest Comments again

Thank you!

2 REPLIES
Ckp
Enthusiast

Re: SQL to combine many rows

Re: SQL to combine many rows

Excellent - I'm getting close. I have duplicate rows, but all the data is concatenated correctly.

Thanks for your help. I had seen some recursive examples, but this one was easier for me to understand