Re: SQL rows to columns

But having said that...

The "pivot" table via CTE is probably a better choice now that I think
about it.

What indexes does VE suggest you need? ~50ms doesn't seem to bad for 12M
records, depending on your hardware.

Charles

On Wed, Mar 13, 2024 at 10:48 AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

If you want two columns, just remove the CONCAT

Just use a join...

select odcom#, odord#, odcmt#,
coalesce(n1.uccmt, '') as comment1,
coalesce(n2.uccmt, '') as comment2
from oeordlod
left join notepduc n1
on n1.ucnott = 'C'
and n1.ucntk = odcmt#
and n1.ucsq3# = 1
left join notepduc n2
on n2.ucnott = 'C'
and n2.ucntk = odcmt#
and n2.ucsq3# = 1
where odord# = '10574107'

Charles

On Wed, Mar 13, 2024 at 9:27 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

Thanks all...

I need this in two columns (eventually maybe up to 5 columns)... so
listagg doesn't seem to do it. Charles Join example also creates a single
column.

I used Birgitta's example - this returns the desired output, but took 60
seconds. My sub-select took 31 milliseconds.
The NOTEPDUC file is large file (12m records).

Run SQL Scripts Explain does not recommend any additional indexes. The
aggregation and table probe were the big winners.

So I'm wondering if the sub-select is the best approach?

Thx


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta Hauser
Sent: Tuesday, March 12, 2024 4:48 PM
To: 'Midrange Systems Technical Discussion' <
midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL rows to columns

You may try something like this (it is at least one Sub-Select less)

With x as (Select Ucntk, Min(Case When UCq3# = 1 Then UCCMT End)
Comment1,
Min(Case When UCq3# = 2 Then UCCMT End) Comment2
from Notepduc
Where UCNOTT = 'C'
and UCSQ3 in (1, 2)
Group By UCNTK
Select Odcom#, Odord#, Odcmt#,
Coalesce(Comment1, '') Comment1, Coalesce(Comment2m '') Comment2
from Oeorlod Left join x on ucntk = Odcmt#

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization - Education - Consulting on IBM i Database and Software
Architect IBM Champion since 2020

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.