Re: SQL rows to columns

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

If you could use one column then:

Select … , ( select listagg(xxx) from …)
> From ….

tirs. 12. mar. 2024 kl. 21.47 skrev Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx
> :

> 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
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> "Train people well enough so they can leave, treat them well enough so they
> don't want to. " (Richard Branson)
> "Learning is experience … everything else is only information!" (Albert
> Einstein)
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
> Wilburn
> Sent: Tuesday, 12 March 2024 19:46
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: SQL rows to columns
>
> Wondering if there a more "elegant" solution to my query?
>
> I have an order lines table (OEORDLOD) and a notes table (NOTEPDUC).
> For each row in the order lines table, there can be up to two rows in the
> notes table (sequence 1 and sequence 2).
> The key is ODCMT# for order lines and UCNTK for Notes.
>
> I'm trying to create one row that includes the comments in two columns
>
> My current SQL statement looks like this:
>
> select odcom#, odord#, odcmt#,
> coalesce((select uccmt from notepduc
>                     where ucnott = 'C' and ucntk = odcmt# and
>                           ucsq3# = 1),'') as comment1,
>                    coalesce((select uccmt from notepduc
>                     where ucnott = 'C' and ucntk = odcmt# and
>                           ucsq3# = 2),'') as commen2 from oeordlod where
> odord# = '10574107'
>
> I have a similar scenario with the Order Header, but it can have up to 5
> notes lines.
> [Logo]<https://www.totalbizfulfillment.com/>    Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> 1 Corporate Dr
> Grantsville, MD 21536
> www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
> --
> 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.
>
>
> --
> 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.